Transpose multiple columns (48 columns into essentially 4)

[c_raviram]

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

[Maskell_Rascal]

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

[john_watkins · Accepted solution]

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.

[Maskell_Rascal]

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.