Multi-Row Formula problem

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

Data Investigation

Accepted answers

Claje

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!

claje_multirowmarker.yxmd

Claje

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.

All comments

neilgallen

@JamesBills Just a guess, but do you have the group by in the multi-row formula checked?

JamesBills

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.

Claje

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.

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!

claje_multirowmarker.yxmd

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335