Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Loops in Alteryx

morak_1449
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

print(i)

i++

else j=1+j

print(j)

j++

end

 

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

 

8 REPLIES 8
jarrod
ACE Emeritus
ACE Emeritus

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

Joe_Mako
12 - Quasar

How about a Multi-Row Formula like:

 

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

 

mrf.png

jarrod
ACE Emeritus
ACE Emeritus

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

morak_1449
7 - Meteor

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

 

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

jarrod
ACE Emeritus
ACE Emeritus

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

Labels