topic Re: crosstable with "2-rows header" in App Development

crosstable with "2-rows header"

Gestion-PSD — Sat, 16 Nov 2024 02:24:53 GMT

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!

Re: crosstable with "2-rows header"

Taoufiq_Zarra — Mon, 18 May 2020 13:13:47 GMT

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 :

Re: crosstable with "2-rows header"

Gestion-PSD — Tue, 19 May 2020 11:32:37 GMT

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