Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
__johan__
Contributor II
Contributor II

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

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

3 Replies
abeyphilip
Creator II
Creator II

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
Champion
Champion

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__
Contributor II
Contributor II
Author

Thank you Abey! This worked really well