I need help with multiple conditions in my app. As you can see from the image below, i need to check for two condition in my data table,
- What the drivers previous month’s drive status.
- The Total Distance they have travelled in the current month.
I know how to get this data, the part I need help with is the If -then-else statement.
I need it to use the previous months drive status, then to check the distance travelled, compare that to the Distance Bands and then assign the correct points and deduct them from the total of 300 Distance points for the month.
Any guidance in the right direction will be appreciated.
The first thing I would do is restructure the table so that you have one row per status level, per distance band - like below:
Form there it is easy, all you need is a simple Query column.
Let’s assume that you have each drivers drive status and distance travelled in your user table, then the Query would be added to your Users table, would target the above Lookup table, and would have the following filters applied:
- Status is This row->Status
- Min Distance is less than or equal to This row->Distance Travelled
- Max Distance is greater than or equal to This row->Distance Travelled
Once you have that, use a Single Value column to retrieve the appropriate number of Points to deduct via the Query.
1 Like
Ok, so if understand you correctly I have to create this reference table and then create the query on the table that has the drive status and distance travelled to get the result i am looking for?
Yes, that’s correct.
Just one thing to watch out for. If you have a driver with (for example) 299.5km, then that wouldn’t match any range. So you might want to adjust those numbers slightly to eliminate that gap.
Thank you so much Darren, that worked perfectly. Much appreciated.
1 Like
