topic Re: Transposing in App Development

Transposing

priyarane — Mon, 03 Dec 2018 12:13:46 GMT

Hello Community,

Do you have any suggestion on below query.

I have data like,

Period,Sales

1,10

2,50

And I want data like below throug script only,

Period1,Period2

10,50

I have done it through the loop but I am checking is there any other way without loop as loop is taking lot of time as volume is so high.

-Priya

Re: Transposing

marcus_sommer — Mon, 03 Dec 2018 12:20:59 GMT

You could use the The-Generic-Load but it's a quite heavy transformation and if your amounts of data are high it will need some time. Usually is your existing datastructure more suitable for the most scenarios - therefore try to rethink your approach and if you really need such transposing.

- Marcus

Re: Transposing

jerryyang756 — Mon, 03 Dec 2018 13:07:09 GMT

Temp1:
LOAD Period,
'Period '&Period as PeriodName,
Sales
FROM
[C:\Users\public\Desktop\File.xlsx]
(ooxml, embedded labels, table is Sheet1);

Drop Field Period;
store Temp1 into File1.csv(txt);
Drop Table Temp1;

Final:
LOAD @1,
@2
FROM
[D:\File1.xlsx]
(qvd, filters(
Transpose()
));

store Final into File2.csv(txt);

drop table Final;

Final2:
LOAD [Period 1],
[Period 2]
FROM
[D:\File2.csv]
(txt, utf8, embedded labels, delimiter is ',', msq, header is 1 lines);