6/17/21: We have completed maintenance for the Search functionality on the Community. If you are seeing any issues, please try to clear your cache first. If the issue persists please email Community@alteryx.com

Alteryx Designer Discussions

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

Loops in Alteryx

7 - Meteor

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



else j=1+j





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


12 - Quasar
12 - Quasar

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?

9 - Comet

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]


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.

7 - Meteor

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


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


We need to come up with the following table:


Grand Opening week Construction Week Index
Week 1Week 41
Week 2Week 42
Week 3Week 43
Week 4Week 41
Week 5Week 42


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

12 - Quasar

How about a Multi-Row Formula like:


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



12 - Quasar
12 - Quasar

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

7 - Meteor

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


7 - Meteor

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.  

12 - Quasar
12 - Quasar

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).