topic Re: Union columns within same table and create a new field for each column value in Alteryx Designer Desktop Discussions

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

Jevish — Mon, 12 Jul 2021 13:42:40 GMT

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

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

Jevish — Mon, 12 Jul 2021 03:30:07 GMT

Iam able to do for one column using arrange tool but i have more than 20 columns with same repeating info 5 times..Is there any other efficient way to do this ?

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

dougperez — Mon, 12 Jul 2021 03:33:53 GMT

Have you tried to make a iterative macro?

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

Jevish — Mon, 12 Jul 2021 03:36:41 GMT

No i have't used macro. Can it be done using macros? plz provide some more details so that i can try it out. thank you!

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

dougperez — Mon, 12 Jul 2021 03:39:02 GMT

Send me this second workflow please

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

Jevish — Mon, 12 Jul 2021 03:55:46 GMT

here is it ..thank you so much for helping out !

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

Jevish — Mon, 12 Jul 2021 14:14:38 GMT

still waiting for a solution on this. Please help.

Thank You.

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

dougperez — Mon, 12 Jul 2021 14:19:14 GMT

Hello Jes! I was sleeping hahahaha
I think that the best solution is to do the arrange tool 5 times then join then by the keys...

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

Jevish — Mon, 12 Jul 2021 14:21:47 GMT

problem is that in my raw data i have 30 more columns. Have to repeat it 30 times 😞

I have found another way, will see if it works or not. Thanks for your help!

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

dougperez — Mon, 12 Jul 2021 16:39:24 GMT

If it works, share with me!
At night (I live in Brasil) I will try again...

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

ArtApa — Mon, 12 Jul 2021 22:47:27 GMT

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

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

Jevish — Tue, 13 Jul 2021 02:03:06 GMT

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!

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

Jevish — Tue, 13 Jul 2021 02:12:14 GMT

@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.

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

ArtApa — Tue, 13 Jul 2021 03:53:42 GMT

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:

Amount
Date
Comment
Check
Change

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