cancel
Showing results for
Did you mean:
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.

Labels (3)

• ### Script

1 Solution

Accepted Solutions
Specialist

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

3 Replies
Specialist

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

Creator

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.

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.