Complex Transpose - Grouping & Renaming Columns

[drewh]

Hello experts - 

I have a data set that looks like the below:

Date	Region	Group 1	Group 2	Group 3	Total	Group 1 %	Group 2 %	Group 3 %	Total %
1-Sep	XX	94	76	66	57	26%	22%	10%	2%
2-Sep	YY	68	71	77	63	2%	41%	8%	36%
3-Sep	ZZ	65	97	59	96	1%	35%	49%	35%
4-Sep	XX	78	92	82	59	3%	41%	30%	26%
5-Sep	YY	57	87	78	60	5%	29%	3%	38%
6-Sep	ZZ	86	71	79	61	37%	40%	2%	48%
7-Sep	AA	50	51	73	98	47%	50%	1%	27%
8-Sep	BB	60	61	94	81	40%	50%	47%	19%
9-Sep	CC	95	51	59	55	26%	22%	37%	3%

And I need to transpose it down to five columns (Date, Region, Group, and a Number & Percent column) like below. However, since the columns are named similarly yet different, I'm having issues figuring out how to get this to transpose correctly. Any advice? Data attached for reference, thanks so much in advance!

Date	Region	Group	Value	%
1-Sep	XX	Group 1	94	26%
1-Sep	XX	Group 2	76	22%
1-Sep	XX	Group 3	66	10%
1-Sep	XX	Total	57	2%
…	…	…	…	…
…	…	…	…	…
…	…	…	…	…

[BenMoss]

Hi!

There's quite a simple solution to this problem by first transposing all of your columns, using a logic to pull out the metric type and the group, before cross-tabbing the data into the structure you desired.

Example attached.

Ben

[BenMoss]

Hi!

There's quite a simple solution to this problem by first transposing all of your columns, using a logic to pull out the metric type and the group, before cross-tabbing the data into the structure you desired.

Example attached.

Ben

[ponraj · Accepted solution]

Here is the sample workflow for your case. Hope this is helpful. 

WorkflowResults

[drewh]

Hi All - thank you so much for the quick responses! Any chance I can get the workflows in Designer 10.0 format? Sorry, forgot to mention I'm a few revs back...

Thanks so much!!

[BenMoss]

You can follow this post to downgrade the workflow:

https://www.theinformationlab.co.uk/2018/07/12/downgrading-alteryx-workflows/

Ben