Transposing

[priyarane]

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

[marcus_sommer]

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

[jerryyang756]

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);