Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
nezuko_kamado
Creator
Creator

How to reshape a data to create a pivot table in the Load eidtor

How to melt the original table into a pivot table in the Load Editor?

The original  data looks like as below

 

 

 

CO1	CO2	CO3	CO4	CO5	CO6	CO7	CO8	CO9	PayPeriod	Month
4	1	6	5	6	4	1	3	8	p1	 	Jan
9	6	5	2	4	2	2	5	5	p2	 	Jan
4	5	1	3	4	6	4	4	2	p3	 	Feb
4	8	1	4	6	3	2	6	2	p4		Feb
2	4	6	2	3	7	3	1	4	p5		Mar
4	5	4	6	7	4	5	7	4	p6	 	Mar
7	2	4	3	3	2	7	7	7	p7	 	Apr
2	7	6	1	2	3	5	6	4	p8	 	Apr
5	5	5	4	3	4	5	4	7	p9	 	May
4	6	6	3	5	1	6	4	7	p10	 	May
9	7	3	6	4	3	5	4	2	p11	 	Jun
2	7	4	2	6	5	8	4	5	p12	 	Jun
5	2	4	8	3	8	4	5	2	p13 	 	Jul
3	3	5	4	4	2	2	2	9	p14 	 	Jul
4	2	6	3	3	7	7	1	5	p15 		Aug
4	2	5	3	2	6	3	5	6	p16	 	Aug
4	0	5	5	2	2	1	6	7	p17	 	Sep
5	2	1	7	1	4	9	5	2	p18	 	Sep
4	3	2	8	1	2	6	3	3	p19	 	Oct
4	3	3	2	3	3	6	7	8	p20	 	Oct
6	2	2	4	1	4	4	4	7	p21	 	Nov
1	4	8	3	5	8	2	2	5	p22		Nov
6	4	4	2	4	5	4	3	3	p23	 	Dec
3	1	4	2	5	3	9	6	6	p24	 	Dec

 

 

 

But I need to reshape the table  into the format as below in the Load script for the further analysis.

nezuko_kamado_0-1695918268354.png

 

 

 

 

 



Labels (3)
1 Solution

Accepted Solutions
Gabbar
Specialist
Specialist

Load Sum(CO1),.....,Sum(CO9), PayPeriod  resident Source group by PayPeriod;

View solution in original post

3 Replies
Gabbar
Specialist
Specialist

Load Sum(CO1),.....,Sum(CO9), PayPeriod  resident Source group by PayPeriod;

L_Hop
Creator
Creator

Hi Nezuko_kamado,

Can you elaborate little bit more about the calculation logic?

In sample data, payperiod P1 & sum(CO1) is 4, however, in your result table it is 7, what is the logic?

If you only want to sum the results aggregated by payperiod and month, you may write somothingh like attached file?

regards.

 

nezuko_kamado
Creator
Creator
Author

Right, Sorry I had to cut the original pivot table that has 19 of COs, because the screencapture was too large.
I updated result table which should be matched now. Thank you.