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

Iterative Macro: Change Formula based on IterationNumber

df
8 - Asteroid

Hello,

 

i was wondering if i could change a formular tool which created 15 new columns to a macro.
The formulas just differ in some numeric values.

Data =

key ab-0b-1b-2b-3R
aaa 36070654050
aab 16070605560
aac 06070657070

 

Formular =
Output Column: Test 1


If [a] >= 1

Then IIF ([b-0] - [R] < 0,0,[b-0] - [R])

Else 0

Endif

Now i wanted to change this formular based on the IterationNumber.

Like:

Formular =
Output Column: Test >>IterationNumber<<


If [a] >= >>IterationNumber<<

Then IIF ([b->>IterationNumber<<] - [R] < 0,0,[b->>IterationNumber<<] - [R])

Else 0

Endif

So in each Run it creates a new column until IterationNumber reached 15 for example.
Can this be done?
My Macro Test with the Data in formular attached

1 REPLY 1
mmenth
11 - Bolide

Hey @df,

 

Cool question-- yes this can be done but it's a bit tricky. If you take a look at the attached I was able to do this by utilizing the multi-field formula tool, and by doing some creative transposing and cross-tabbing to treat the column names as fields. The reason this becomes tricky is that in Alteryx you can't use the iteration number within a column name in a formula. To overcome that I had to first identify the column of interest, then change it to a generic name, in this case 'b'. 

 

Also I had to add an input where you explicitly state the max number of iterations (3 in the example you gave me, but for your full data you'll have to change that to 15). Without that logic it still worked but threw errors as it kept trying to iterate beyond 3.

 

Let me know if you have any questions about the macro, I know it's a bit complex!

 

Best,

mmenth

Labels