Transformation

[anwerm2]

Hi

I have been trying to transform a table but not getting my desired result. Could some one please help me or provide any specific tools available in Alteryx. I have attached the excel file which has two tabs  Sheet1 is table and result is what I want. Thank you in advance.

Regards

M. Anwer

DM	tree	BU_DM1	BU_DM1	RC_DM2	RC_DM2	ACC_DM3	ACC_DM3
1	00610	AB	AAB
1	00919	AC	AAC
1	01129	AD	AAD
2	0000			BA	BAA
2	0487			BB	BAB
2	0623			BC	BAC
3	0102					CA	CAA
3	1000					CB	CAB
3	1002					CC	CAD

Result I want

tree	BU_DM1	BU_DM1	tree	RC_DM2	RC_DM2	tree	ACC_DM3	ACC_DM3
00610	AB	AAB	0000	BA	BAA	0102	CA	CAA
00919	AC	AAC	0487	BB	BAB	1000	CB	CAB
01129	AD	AAD	0623	BC	BAC	1002	CC	CAD

[MilindG]

See if this works

[danilang]

Hi @anwerm2 

Here's a dynamic way to do this that avoids having to change the workflow if the number of DMs changes

After removing the null rows from the input data, use a MultiRow formula tool to add the row number that will be in your final output.  Transpose, Remove the null values and build the renumbered "Tree" column headers.  The next Multi-Row formula tool is there to add in columns numbers to compensate for the fact that the Crosstab will create the columns in alphabetical order if you use the final column names directly in the tool.  The final Dynamic Rename tool just renames the columns from the numbers to final output column names 

Dan

[KimLamNg]

I renamed the input headers a bit but the logic should still be applicable.

Spoiler

[KimLamNg · Accepted solution]

@danilang I really like the way you set up the column number so that the order is retained after the Cross tab. I was struggling with it.

[anwerm2 · Accepted solution]

Kim you are GREAT

My columns were wrong headings. For each dimension it was level 1 and level 2. Kim corrected it.  Thank you

Danilang solution was also good and dynamic.

[prakashkaur189 · Accepted solution]

This is my 1st used case. It was hard for me to understand and bring the data in a particular layout but I am happy to solve it in the last. I have used quite basic tools and have kept it simple. My code is updated here for reference,

[anwerm2 · Accepted solution]

Thank you