Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
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