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?
How about a Multi-Row Formula like:
IF [Grand Opened week]=[Construction week]THEN 1ELSE [Row-1:Index]+1ENDIF
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?
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 1elseif !isnull([row-1:i]) and date_1 < date_2 then [row-1:i]+1elseif isnull([row-1:i]) and date_1 >= date_2 then 0else [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.
Right. A use case for the loop will be the following:
We need to come up with the following table:
The moment Grand Opening week reaches to Construction week, the index get reset.
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.
