input data column is dynamic formula is not working

[philosophy22]

Hello,

My first post here so I apologize in advance if broke Labels/Posting Rules.

My pain point is the input file format is not fixed, it have different columns, some times it has 4 or sometimes it has 6, I need to calculate percentage of these columns and use percentage to calculate result for each component. 

When I use formula tool to calculate these columns, it will pop up error message, because their columns is not same, some column is missing, but some column is unnecessary.

For example, this question is simple in excel, because we can manually change formula range.

However in alteryx, is there any quick solution to solve this question? Thanks in advance

[DavidP · Accepted solution]

Hi @philosophy22 

Welcome to the Community!

In this case, I would transpose the data, but with a RecordID. Now you can do you calculations in the transposed state where all the data is in the [Value] column and use a CrossTab to return the data to the desired format. I attached a mocked up example - Row 0 is the calculated percentage and the CrossTab can give you a Total column.

Another option would be to use the Multi-field formula and use the [_CurrentField_] notation in your formula. 

[philosophy22]

Really helpful solution, David.

After transposing, everything become easy to go. Many thanks!