Transpose multiple columns (48 columns into essentially 4)
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Experts,
I have a following table which consist of quantity and value$.
Column with the date format as YYYY-MM-DD have quantity, whereas columns with date format as $ MMM-YY have values$.
Total 48 columns (2 years X 12 months X 2 measures(quantity and value))
Input data below:
Country | Cluster | 2021-01-01 | 2021-02-01 | 2021-03-01 | 2021-04-01 | 2021-05-01 | 2021-06-01 | 2021-07-01 | 2021-08-01 | 2021-09-01 | 2021-10-01 | 2021-11-01 | 2021-12-01 | $ Jan-21 | $ Feb-21 | $ Mar-21 | $ Apr-21 | $ May-21 | $ Jun-21 | $ Jul-21 | $ Aug-21 | $ Sep-21 | $ Oct-21 | $ Nov-21 | $ Dec-21 | 2022-01-22 | 2022-02-22 | 2022-03-22 | 2022-04-01 | 2022-05-01 | 2022-06-01 | 2022-07-01 | 2022-08-01 | 2022-09-01 | 2022-10-01 | 2022-11-01 | 2022-12-01 | $ Jan-22 | $ Feb-22 | $ Mar-22 | $ Apr-22 | $ May-22 | $ Jun-22 | $ Jul-22 | $ Aug-22 | $ Sep-22 | $ Oct-22 | $ Nov-22 | $ Dec-22 |
A | P | - | - | - | 0 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
B | Q | - | - | - | 0 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
C | R | 20,634 | 5,490 | 10,206 | 11,622 | 12,600 | 14,640 | 9,931 | 16,003 | 16,637 | 13,300 | 13,642 | 9,918 | 174 | 47 | 87 | 99 | 107 | 125 | 91 | 147 | 153 | 122 | 126 | 91 | 20,977 | 15,457 | 22,081 | 16,098 | 18,455 | 19,995 | 16,561 | 17,002 | 20,127 | 15,457 | 17,899 | 20,703 | 180 | 132 | 189 | 138 | 158 | 171 | 142 | 146 | 172 | 132 | 153 | 177 |
A | P | 21,649 | 7,800 | 23,324 | 20,066 | 7,726 | 15,324 | 13,599 | 15,047 | 15,441 | 12,228 | 12,543 | 11,386 | 3,618 | 1,327 | 3,903 | 3,227 | 1,292 | 2,507 | 2,236 | 2,475 | 2,539 | 2,011 | 2,063 | 1,873 | 15,568 | 11,471 | 16,387 | 11,947 | 13,696 | 14,839 | 12,290 | 12,618 | 14,937 | 11,471 | 13,283 | 15,365 | 2,304 | 1,698 | 2,425 | 1,768 | 2,027 | 2,196 | 1,819 | 1,868 | 2,211 | 1,698 | 1,966 | 2,274 |
B | Q | 40,499 | 19,200 | 25,644 | 16,380 | 21,300 | 23,580 | 21,050 | 24,493 | 25,281 | 23,231 | 23,829 | 22,301 | 1,743 | 826 | 1,114 | 702 | 913 | 1,011 | 910 | 1,059 | 1,093 | 1,004 | 1,030 | 964 | 32,352 | 23,838 | 34,055 | 24,826 | 28,463 | 30,837 | 25,541 | 26,222 | 31,041 | 23,838 | 27,604 | 31,930 | 1,301 | 959 | 1,369 | 998 | 1,145 | 1,240 | 1,027 | 1,054 | 1,248 | 959 | 1,110 | 1,284 |
C | R | 13,972 | 5,460 | 8,856 | 7,668 | 6,618 | 7,608 | 7,504 | 7,735 | 7,591 | 6,245 | 6,406 | 5,631 | 2,351 | 950 | 1,478 | 1,239 | 1,144 | 1,253 | 1,234 | 1,272 | 1,248 | 1,027 | 1,054 | 926 | 7,523 | 5,543 | 7,919 | 5,773 | 6,618 | 7,171 | 5,939 | 6,097 | 7,218 | 5,543 | 6,419 | 7,425 | 1,114 | 820 | 1,172 | 854 | 980 | 1,061 | 879 | 902 | 1,068 | 820 | 950 | 1,099 |
A | P | 21,780 | 2,700 | 9,720 | 10,260 | 10,224 | 8,982 | 8,353 | 14,458 | 13,168 | 12,159 | 12,453 | 10,272 | 192 | 24 | 86 | 50 | 49 | 43 | 74 | 128 | 117 | 108 | 110 | 91 | 20,746 | 15,286 | 21,838 | 15,920 | 18,252 | 19,774 | 16,378 | 16,815 | 19,905 | 15,286 | 17,701 | 20,475 | 171 | 126 | 180 | 131 | 150 | 163 | 135 | 138 | 164 | 126 | 146 | 169 |
B | Q | 18,984 | 8,328 | 13,572 | 4,632 | 10,835 | 13,188 | 10,669 | 12,007 | 12,243 | 8,099 | 8,308 | 7,542 | 3,212 | 1,378 | 2,250 | 754 | 1,766 | 2,150 | 1,755 | 1,975 | 2,013 | 1,332 | 1,366 | 1,240 | 9,213 | 6,789 | 9,698 | 7,070 | 8,106 | 8,782 | 7,274 | 7,468 | 8,840 | 6,789 | 7,861 | 9,093 | 1,364 | 1,005 | 1,435 | 1,046 | 1,200 | 1,300 | 1,077 | 1,105 | 1,308 | 1,005 | 1,164 | 1,346 |
C | R | 19,849 | 10,340 | 12,461 | 9,230 | 10,200 | 11,712 | 8,554 | 13,068 | 13,489 | 12,395 | 12,714 | 11,899 | 854 | 445 | 536 | 396 | 437 | 502 | 370 | 565 | 583 | 536 | 550 | 515 | 16,973 | 12,507 | 17,866 | 13,025 | 14,933 | 16,178 | 13,400 | 13,757 | 16,285 | 12,507 | 14,482 | 16,751 | 683 | 503 | 718 | 524 | 600 | 651 | 539 | 553 | 655 | 503 | 582 | 674 |
A | P | 13,910 | 4,092 | 8,004 | 4,416 | 5,400 | 5,580 | 6,393 | 7,120 | 7,193 | 5,741 | 5,888 | 5,345 | 2,337 | 694 | 1,327 | 736 | 893 | 918 | 1,051 | 1,171 | 1,183 | 944 | 968 | 879 | 6,774 | 4,991 | 7,130 | 5,198 | 5,959 | 6,456 | 5,348 | 5,490 | 6,499 | 4,991 | 5,779 | 6,685 | 1,003 | 739 | 1,055 | 769 | 882 | 956 | 792 | 813 | 962 | 739 | 855 | 989 |
Output data:
Those 48 columns need to be transposed to 3 columns below (Month, year, Quantity and value) like below
Country | Cluster | Month | Year | Quantity | Value $ |
A | P | Jan | 2021 | ||
B | Q | Feb | 2021 | ||
C | R | Mar | 2021 | ||
A | P | Apr | 2021 | ||
B | Q | May | 2021 | ||
C | R | Jun | 2022 | ||
A | P | Jul | 2022 | ||
B | Q | Aug | 2022 | ||
C | R | Sep | 2022 | ||
A | P | Oct | 2022 |
I know this can be achieved with two transpose tools, but I am curious to know any alternative approach (maybe combination of develop tools and transpose)
Problem with transpose tool is that sometimes it automatically deselect certain columns and hence a need to configure transpose tool again (for every cycle run).
Attached is the sample dataset.
Regards, Ravi
Solved! Go to Solution.
- Labels:
- Tips and Tricks
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @c_raviram
I'm not sure I understand the problem. The Transpose Tool has a Dynamic or Unknown Columns option for your Data Columns, so as long as your Key Columns don't change you should be fine with this approach.
I put together a sample workflow based on your input, and was able to get to the desired output using Transpose, Formula, and Crosstab.
Attached is the packaged workflow for you to try out.
Cheers!
Phil
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Many issues come about from data just not being stored in a "reusable" manner. Generally when you get things as spreadsheets designed by a business user with no database design background. You can do all the splits easily and pretty foolproof by just trying to put the data back to a consistent format. Attached is a sample workflow. I didn't shoot for most efficient as you had a few quirks around dates/etc., but you will get the idea.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I again don't think I fully understand the desired output to this problem, b/c the solution accepted here leaves all the records with duplicate entries for the same date.
