community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Multi-Row Formula problem

Highlighted
Meteor

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)

 

CarDateDistanceMarker
101/01/2019300 
102/01/201950 
103/01/201901
104/01/20190 
105/01/20190 
106/01/20190 
107/01/20190 
108/01/20190 
109/01/20190 
110/01/20190 
111/01/20190 
112/01/20190 
113/01/20190 
114/01/20190 
115/01/20190 
116/01/20190 
117/01/2019400 
118/01/2019500 
201/01/2019350 
202/01/20190 
203/01/20190 
204/01/20190 
205/01/20190 
206/01/2019500 
207/01/2019550 

 

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

 

 

Quasar

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

Meteor

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. 

 

Magnetar
Magnetar

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!

Quasar

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.

Magnetar
Magnetar

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.

Meteor

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. 

 

Labels