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.
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
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.
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.