Applying Formula To Multiple Fields

[henrymk5]

I'm using a small sample for my question, but I am trying to find a way to apply the same formula to multiple fields.

I have Cash1, Cash1_right, Cash2, Cash2_Right, Cash3, Cash3_Right etc.

I need the formula Cash1 - Cash1_Right to be applied followed by formula Cash2 - Cash2_right then Cash2 - Cash2_right etc.

I have thousands of matching fields that I require the differences for and do not want to manually input a separate formula for each fields.

Is there an easier way for me to obtain the differences between the field pairings.

Attached is my sample data.

Thank you!

[Simha]

Hi,

Could this work?

Simha

[fmvizcaino]

Hi @henrymk5 ,

Example attached.

Let me know if this works for you.

Best,

Fernando Vizcaino

[estherb47 · Accepted solution]

This is why we need an indirect function in Alteryx!

@Simha , my approach differs a touch from @fmvizcaino 's, so it's a matter of preference.

Explanation: After the join, added a record ID tool, because I'm going to transpose the data and want to keep the integrity of the rows. Used a formula tool to change the naming from the Join tool into, for example, cash1_right

Then a sort and multirow formula. The multirow formula subtracts the value in the next row from the value in the current row. The Sample tool ignores every other row (we don't need that for the new difference calculation) I needed to adjust field size with Select in order to add the "_Difference" to the newly calculated field, and remove fields that aren't needed anymore. Unioned that together with the original transposed list, and crosstabbed back to a table.

Let me know if this works for you. Cheers! Esther