Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Bucharest on Sept 18th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Gestion-PSD
Creator II
Creator II

crosstable with "2-rows header"

Hi community!

We're blocked on an app development because we don't know how to convert an Excel table into a QS table as we need in our data model.

We need to load Excel sheet with crosstable load, but we want the second row as a field, not values.

This is part of the Excel table:

AccountJANFEBMAR
TypeAAF
Books554645245
DVDs608470192
CDs1341860

 

The results of a simple crosstable load is:

AccountPeriodeSum
TypeJANA
TypeFEBA
TypeMARF
BooksJAN554
BooksFEB645
BooksMAR245
DVDsJAN608
DVDsFEB470
DVDsMAR192
CDsJAN134
CDsFEB186
CDsMAR0

 

But we want:

AccountPeriodeSumType
BooksJAN554A
BooksFEB645A
BooksMAR245F
DVDsJAN608A
DVDsFEB470A
DVDsMAR192F
CDsJAN134A
CDsFEB186A
CDsMAR0F

 

Do you know a way to get we want? I'd looking for this question in the Community, but the threads I found didn't answer this.

 

Thanks all!

Labels (1)
1 Solution

Accepted Solutions
Taoufiq_Zarra

I hope this will help you :

source file Excel:

Capture.PNG

 

then the script :

DataDate:
CrossTable(Periode, Data)
LOAD * FROM
C:\Users\admin\Downloads\1805.xlsx
(ooxml, embedded labels, table is Feuil1, filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Type')))
));



Datatype:

CrossTable(Typetmp, Data)
LOAD * FROM
C:\Users\admin\Downloads\1805.xlsx
(ooxml, embedded labels, header is 1 lines, table is Feuil1);


Data:

load Account,Periode,Data as Sum resident DataDate;

join 

load Type as Account,purgechar(Typetmp,'0123456789') as Type,Data as Sum resident Datatype;

drop table Datatype,DataDate;

 

output :

Capture.JPG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

2 Replies
Taoufiq_Zarra

I hope this will help you :

source file Excel:

Capture.PNG

 

then the script :

DataDate:
CrossTable(Periode, Data)
LOAD * FROM
C:\Users\admin\Downloads\1805.xlsx
(ooxml, embedded labels, table is Feuil1, filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Type')))
));



Datatype:

CrossTable(Typetmp, Data)
LOAD * FROM
C:\Users\admin\Downloads\1805.xlsx
(ooxml, embedded labels, header is 1 lines, table is Feuil1);


Data:

load Account,Periode,Data as Sum resident DataDate;

join 

load Type as Account,purgechar(Typetmp,'0123456789') as Type,Data as Sum resident Datatype;

drop table Datatype,DataDate;

 

output :

Capture.JPG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Gestion-PSD
Creator II
Creator II
Author

Wow!

It seems quite elegant solution.

I ignored that we can filter rows in excel load like that, but it isn't the same than using 'where' clasue?

 

I don't try your solution yet, but it seems to run ok.

 

Thanks!!!