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% |
… | … | … | … | … |
… | … | … | … | … |
… | … | … | … | … |
Solved! Go to Solution.
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
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!!