I am trying to get assign a marker when a car doesn't travel for more than 10 days in a row.
See Table below (Marker column)
Car | Date | Distance | Marker |
1 | 01/01/2019 | 300 | |
1 | 02/01/2019 | 50 | |
1 | 03/01/2019 | 0 | 1 |
1 | 04/01/2019 | 0 | |
1 | 05/01/2019 | 0 | |
1 | 06/01/2019 | 0 | |
1 | 07/01/2019 | 0 | |
1 | 08/01/2019 | 0 | |
1 | 09/01/2019 | 0 | |
1 | 10/01/2019 | 0 | |
1 | 11/01/2019 | 0 | |
1 | 12/01/2019 | 0 | |
1 | 13/01/2019 | 0 | |
1 | 14/01/2019 | 0 | |
1 | 15/01/2019 | 0 | |
1 | 16/01/2019 | 0 | |
1 | 17/01/2019 | 400 | |
1 | 18/01/2019 | 500 | |
2 | 01/01/2019 | 350 | |
2 | 02/01/2019 | 0 | |
2 | 03/01/2019 | 0 | |
2 | 04/01/2019 | 0 | |
2 | 05/01/2019 | 0 | |
2 | 06/01/2019 | 500 | |
2 | 07/01/2019 | 550 |
At the moment I am using this formula in a Multi-row formula tool:
(I later remove the excess 1's so just left with a 1 a the start of the period of inactivity)
At the moment though my formula assigns a 1 for Car 2 but it shouldn't.
IF [Distance] <50
AND
[Row+1:Distance] < 1
AND
[Row+2:Distance] < 1
AND
[Row+3:Distance] < 1
AND
[Row+4:Distance] < 1
AND
[Row+5:Distance] < 1
AND
[Row+6:Distance] < 1
AND
[Row+7:Distance] < 1
AND
[Row+8:Distance] < 1
AND
[Row+9:Distance] < 1
AND
[Row+10:Distance] < 1
THEN
1
ELSE
0
ENDIF
If there is (there definitely is) another way of doing please please could i have some suggestions
Thanks
James
Solved! Go to Solution.
@JamesBills Just a guess, but do you have the group by in the multi-row formula checked?
Yes i have the group by checked. @neilgallen
For some reason though it still assigns a marker if the distance is 0 in the last record available.
Hi, While you can potentially do all of this in one Multi-Row formula, I think the logic gets somewhat complex and I would personally prefer to break this out into multiple tools.
I've attached an example workflow that shows how I would build this out.
I started by creating a boolean value for if someone had traveled on any given day using the formula:
IF Distance >= 50 Then 1 Else 0 ENDIF
I then used this in a Multi-Row formula (Grouped by Car) to create a new field that I called RecordID (but this name could/should probably change).
IF Traveled OR (!Traveled AND [Row-1:Traveled]) THEN [Row-1:RecordID]+1 ELSE [Row-1:RecordID] ENDIF
This will basically increment the recordid unless the car did not travel today AND yesterday.
Then I used a Summarize tool to Group by Car and RecordId, and get a count of RecordID. I joined this back to the initial dataset (Join on Car and RecordID), and created a preliminary "Marker" column that I called FirstMarker
If !Traveled and Count >=10 THEN 1 Else '' ENDIF
Then I used a final Multi-Row Formula to clean up and create the final Marker column:
IF [FirstMarker] = '1' AND [Row-1:FirstMarker] != '1' THEN 1 Else '' ENDIF
Finally, I deselected some of the extra fields I created with a Select tool.
Hope this helps!
What do you have as the option for 'Values for rows that don't exist'? Likely it's seeing that the distance is less than 50 and all the subsequent + rows don't exist.
Second post - wanted to quickly reply to the following:
@JamesBills wrote:Yes i have the group by checked. @neilgallen
For some reason though it still assigns a marker if the distance is 0 in the last record available.
If you change the "Values for Rows that don't exist" to NULL from "0 or empty" or "set to value of closest row", that should correct your current formula.
Thank you @neilgallen and @Claje
Changing the field to Null makes that work now.
Also thanks for another working version @Claje . Would never have thought of doing it like that.