transpose data when loading excel

[Anonymous]

Hello, I have one difficulty when loading excel data as attached.

Basically the original data is this,

SKU	Prospective	2018 P01 W1	2018 P01 W2	2018 P01 W3	2018 P01 W4	2018 P02 W1	2018 P02 W2	2018 P02 W3	2018 P02 W4	2018 P02 W5
120	on hand units	4,640	4,648	4,641	4,330	4,277	4,291	4,754	4,426	4,817
120	on hand cost	50,263	50,373	50,313	46,948	46,375	46,557	51,554	47,998	52,242
120	pos units	1,429	1,725	1,511	1,530	1,812	1,793	1,993	2,310	1,945
120	pos sales	19,273	23,266	20,379	20,636	24,439	24,183	26,880	31,156	26,233
25465	on hand units	12	12	12	12	12	12	20	20	18
25465	on hand cost	54	54	54	54	54	54	90	90	81
25465	pos units	2	0	0	0	0	0	4	0	2
25465	pos sales	11	0	0	0	0	0	22	0	11

I want it loaded as this:

SKU	Time	on hand units	on hand cost	pos units	pos sales
120	2018 P01 W1	4,640	50,263	1,429	19,273
120	2018 P01 W2	4,648	50,373	1,725	23,266
120	2018 P01 W3	4,641	50,313	1,511	20,379
120	2018 P01 W4	4,330	46,948	1,530	20,636
120	2018 P02 W1	4,277	46,375	1,812	24,439
120	2018 P02 W2	4,291	46,557	1,793	24,183
120	2018 P02 W3	4,754	51,554	1,993	26,880
120	2018 P02 W4	4,426	47,998	2,310	31,156
120	2018 P02 W5	4,817	52,242	1,945	26,233
25465	2018 P01 W1	12	54	2	11
25465	2018 P01 W2	12	54	0	0
25465	2018 P01 W3	12	54	0	0
25465	2018 P01 W4	12	54	0	0
25465	2018 P02 W1	12	54	0	0
25465	2018 P02 W2	12	54	0	0
25465	2018 P02 W3	20	90	4	22
25465	2018 P02 W4	20	90	0	0
25465	2018 P02 W5	18	81	2	11

obviously the time is expending week by week. can anyone help to write script? thanks in advance.

[Quy_Nguyen · Accepted solution]

Hi,

Try this script (rename the connection with your connection)

A:
CrossTable(Time, Value, 2)
LOAD
    SKU,
    Prospective,
    "2018 P01 W1",
    "2018 P01 W2",
    "2018 P01 W3",
    "2018 P01 W4",
    "2018 P02 W1",
    "2018 P02 W2",
    "2018 P02 W3",
    "2018 P02 W4",
    "2018 P02 W5"
FROM [lib://Desktop/transpose sample data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

B:
Load SKU,
 Time,
     If(Prospective = 'on hand units',Value) As "on hand units",
     If(Prospective = 'on hand cost',Value) As "on hand cost",
     If(Prospective = 'pos units',Value) As "pos units",
     If(Prospective = 'pos sales',Value) As "pos sales"
Resident A;
Drop Table A;

FinalData:
NoConcatenate
Load 
 SKU,
    Time,
    Sum("on hand units") As "on hand units",
    Sum("on hand cost") As "on hand cost",
    Sum("pos units") As "pos units",
    Sum("pos sales") As "pos sales"
Resident B
Group by SKU, Time;
Drop Table B;

[Anonymous]

Thanks for the help, Quy.

I tried, but got following error

Field "Name" not found.

I am pretty sure the connection to data was correct, and nowhere using the field "Name". please see screen shot. Any idea? thx.

[Anonymous]

After trying couple times, I gave it up and created a brand new app, the script then worked very well.

Can I ask another question? why sum here?

Sum("on hand units") As "on hand units",

what about if a KPI means nothing on sum? for instance, avg retail price? or growth %? then what to do with them?

Thanks for further explanation if possible.