Is it possible to transpose certain columns from an excel file in QV script to wanted format

[__johan__]

Hi,

I'm really struggling with creating a table in QlikView script to a certain format. Please view attached Excel-file:

"InsertData" tab displays how structure is today

"WantedFormat" tab displays what format I wish to get.

If anyone can help me it would me much appreciated.

Br,

Johan

[abeyphilip]

Hi Johan,

Try if this helps:

Data1:
CrossTable(DummyCol1, StoreOrgNr, 3)
LOAD SalesRegDate,
Campaign,
Promo,
StoreOrgNr1,
StoreOrgNr2,
StoreOrgNr3,
StoreOrgNr4,
StoreOrgNr5
FROM
[<<Path>>\Testing_Excel.xlsx]
(ooxml, embedded labels, table is InsertData);

Data2:
CrossTable(DummyCol2, PCode, 3)
LOAD SalesRegDate,
Campaign,
Promo,
Pcode1
FROM
[<<Path>>\Testing_Excel.xlsx]
(ooxml, embedded labels, table is InsertData);

Data3:
CrossTable(DummyCol3, TCode, 3)
LOAD SalesRegDate,
Campaign,
Promo,
Tcode1,
Tcode2
FROM
[<<Path>>\Testing_Excel.xlsx]
(ooxml, embedded labels, table is InsertData);

left join (Data1) load * resident Data2;
left join (Data1) load * resident Data3;

drop tables Data2, Data3;
drop Fields DummyCol1, DummyCol2, DummyCol3;

Regards,

Abey

[tamilarasu]

Hi Johan,

Try this.

Data:
LOAD SalesRegDate,
Campaign,
Promo,
StoreOrgNr1,
StoreOrgNr2,
StoreOrgNr3,
StoreOrgNr4,
StoreOrgNr5,
Pcode1 as Pcode,
Tcode1 as Tcode
FROM
[..\..\Downloads\QlikViewHelpCrossTableTranspose.xlsx]
(ooxml, embedded labels, table is InsertData);

Concatenate (Data)

LOAD SalesRegDate,
Campaign,
Promo,
StoreOrgNr1,
StoreOrgNr2,
StoreOrgNr3,
StoreOrgNr4,
StoreOrgNr5,
Pcode1 as Pcode, 
Tcode2 as Tcode
FROM
[..\..\Downloads\QlikViewHelpCrossTableTranspose.xlsx]
(ooxml, embedded labels, table is InsertData) where Len(Trim(Tcode2))>0;

Final:
CrossTable(ColumnName, StoreOrgNr, 5)
LOAD SalesRegDate,
Campaign,
Promo,
Pcode,
Tcode,
StoreOrgNr1,
StoreOrgNr2,
StoreOrgNr3,
StoreOrgNr4,
StoreOrgNr5     
Resident Data;

DROP Table Data;

[__johan__]

Thank you Abey! This worked really well