cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

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

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

SOLVED

## Multi-Row Formula problem

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)

 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

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

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.

Highlighted
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