Loops in Alteryx

[morak_1449]

I have the following suede code that I need to implement in Alteryx: 

i=0 , j=0

if date_1<date_2

then i=1+i

print(i)

i++

else j=1+j

print(j)

j++

end

Any ideas of how this could be configured in Alteryx Designer?

[jarrod]

Typically, when the word loop comes into play, it translates to "Iterative macro". However, if i had a list of records with two dates i'm comparing, then i'd just run a multi row tool or summarize (depending on what i'm trying to accomplish).

Do you have a sample use case for the code? or sample data?

[Storm]

And I assume the loop aspect of it is....?  You'd want to do this for all records in a dataset?

My intuitive reaction would be to use the multi-row tool.  First sort your data in the correct order for these rules to apply; create two fields [i] and [j]; set them to null() in the formula tool; then use the multi-row tool with code like this (this is off top of head so I might have a tiny syntax error):

if isnull([row-1:i]) and date_1 < date_2 then 1
elseif !isnull([row-1:i]) and date_1 < date_2 then [row-1:i]+1
elseif isnull([row-1:i]) and date_1 >= date_2 then 0
else [row-1:i]
endif

Assuming I wrote it correctly, this says, if it's the first record, and date_1 < date_2, then set i equal to 1 (0 + 1); but if it isn't the first record (that is, i has a value) and date_1 < date_2, then add 1 to whatever i was in the row above; but if it's the first record and date_1 not < date_2, then 0 (i started at 0 and should stay 0 for this record); but if it isn't the first record (that is, i has a value) and date_1 not < date_2, then keep the same value i had in the row above.

You'd apply this code to field "i" and then also apply it to field "j", changing the references to be "j" instead of "i" and also making sure the date comparisons are flipflopped.

[morak_1449]

Right. A use case for the loop will be the following:

Grand Opened week	Construction week
week 1	week 4
week 2	week 4
week 3	week 4
week 4	week 4
week 5	week 4

We need to come up with the following table:

Grand Opening week	Construction Week	Index
Week 1	Week 4	1
Week 2	Week 4	2
Week 3	Week 4	3
Week 4	Week 4	1
Week 5	Week 4	2

The moment Grand Opening week reaches to Construction week, the index get reset.

[Joe_Mako · Accepted solution]

How about a Multi-Row Formula like:

IF [Grand Opened week]=[Construction week]
THEN 1
ELSE [Row-1:Index]+1
ENDIF

[jarrod]

^^^ Joe's got the answer ^^^

[morak_1449]

@Joe_Mako thank you so much for your reply. That's exactly what I was looking for. 

[morak_1449]

That's very close to what I was looking for. Thank you for suggesting Multi-row formula. The problem with the expression is that the second and forth line never gets through because the primary values of i and j are set as null.  

[jarrod]

you can try creating the fields in the multi row tool directly, which should deal with the null() value just fine (instead using a value=0).