crosstable with "2-rows header"

[Gestion-PSD]

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:

Account	JAN	FEB	MAR
Type	A	A	F
Books	554	645	245
DVDs	608	470	192
CDs	134	186	0

The results of a simple crosstable load is:

Account	Periode	Sum
Type	JAN	A
Type	FEB	A
Type	MAR	F
Books	JAN	554
Books	FEB	645
Books	MAR	245
DVDs	JAN	608
DVDs	FEB	470
DVDs	MAR	192
CDs	JAN	134
CDs	FEB	186
CDs	MAR	0

But we want:

Account	Periode	Sum	Type
Books	JAN	554	A
Books	FEB	645	A
Books	MAR	245	F
DVDs	JAN	608	A
DVDs	FEB	470	A
DVDs	MAR	192	F
CDs	JAN	134	A
CDs	FEB	186	A
CDs	MAR	0	F

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!

[Taoufiq_Zarra · Accepted solution]

I hope this will help you :

source file Excel:

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 :

[Gestion-PSD]

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