Alteryx Designer Desktop Discussions

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

Dynamically reference columns in a formula using Multi-Field tool

Maria_M12
5 - Atom

Hello Alteryx Community,

 

Is it possible to dynamically reference fields in the multi-field tool?

 

I have a dataset with 60 columns representing each month for a 5 year time span.  The dataset only contains values for the first 12 months, all other fields are set to 0.  I need to update columns 13 through 60 with a new value based on the value from the same month in the previous year.

 

In the screenshot below, column 13 would equal column 1 multiplied by some variable; column 14 would equal column 2 multiplied by the variable; etc

 

Test Data Set.png

 

I know I can accomplish this by just writing 40+ formulas in the formula tool, but I was hoping to find a better way.

 

Thank you very much for your help!

 

 

 

3 REPLIES 3
jdminton
12 - Quasar

Hi @Maria_M12 . I would suggest adding a record ID and using transpose to move the data into rows. Then use the multi-row formula to create a formula using something like Row-12 * 1.05 (or whatever change you want to make from the 1-12 columns. You will need to group the data by record ID in the multi-row tool. If you need help with this, please attach some data and/or your started workflow. I hope that helps!

Maria_M12
5 - Atom

Hi @jdminton

 

That worked perfectly.  Thank you very much!

jdminton
12 - Quasar

Great! Please mark the answer as solved.

Labels
Top Solution Authors