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 DMtreeBU_DM1BU_DM1RC_DM2RC_DM2ACC_DM3ACC_DM3100610ABAAB 100919ACAAC 101129ADAAD 20000 BABAA 20487 BBBAB 20623 BCBAC 30102 CACAA 31000 CBCAB 31002 CCCAD Result I want treeBU_DM1BU_DM1treeRC_DM2RC_DM2treeACC_DM3ACC_DM300610ABAAB0000BABAA0102CACAA00919ACAAC0487BBBAB1000CBCAB01129ADAAD0623BCBAC1002CCCAD Data to arrange paralel.xlsx

Transformation

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

Data to arrange paralel.xlsx

Transformation

Accepted answers

KimLamNg

@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

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

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,

Transformation.yxmd

anwerm2

Thank you

All comments

MilindG

See if this works

Transformation.yxmd

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