Iterative Macro: Change Formula based on IterationNumber

df

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	a	b-0	b-1	b-2	b-3	R
aaa	3	60	70	65	40	50
aab	1	60	70	60	55	60
aac	0	60	70	65	70	70

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

Macro - Test.yxzp

mmenth

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

iterate multiple columns.yxzp