Union columns within same table and create a new field for each column value

[Jevish]

I am stuck with a problem. Need help with union multiple columns within a same table and then create a new column which will show which column name of the union data. I tried transpose, arrange and union tool but am not getting the results. I have attached the sample raw data and expected output data.

Can someone please help out !

As you can see in the attachment raw input data tab , I need to union the first amount, second amount , third amount, fourth amount and fifth amount into one column and then create another column which will tell which amount corresponds to which column. I need to repeat it for all the remaining columns which have same data (highlighted in the different colors).

Please find the attached file with expected output. Please advise or suggest a way to do this in Alteryx.

Thank You

[ArtApa · Accepted solution]

Hi @Jevish - I did it dynamic for you. It will work for as many columns as your file will have. Cheers:

[Jevish]

Thank you so much ! I was struggling and had to manually create these columns and then union them.. Your trick saved me so much time.

Appreciate it so much !!Thanks again!

[Jevish]

@ArtApa I understood your work flow expect the formula you used - FLOOR(([ID]-1)/5) .

Just wondering why you did this ? If you can help me learn.

[ArtApa]

Hi @Jevish - I am glad to know it helped you. 

This formula was just a quick way to define columns/rows for Cross Tab (9). You have 5 columns in your data set:

1. Amount
2. Date
3. Comment
4. Check
5. Change

Try to deselect the "New" column in Cross Tab (9) and see what will happen.