Excel with multiple header lines

[NW1965]

Hi Folks

I have an excel file which I want to convert using cross-table, which I'm happy to do, but the issue that is causing some problems for me is that the columnar data has three heading rows, all of which I want to convert to attributes.

For example:

			Day	Mon	Tue	Wed	Thu	Fri	Mon
			Date	04.11.19	05.11.19	06.11.19	07.11.19	08.11.19	11.11.19
			Working Day+	WD+1	WD+2	WD+3	WD+4	WD+5	WD+6
			Working Day-	WD-21	WD-20	WD-19	WD-18	WD-17	WD-16
Region	City	Store	Product
EMEA	London	X1	ABC	12	34	24	22	18	19
EMEA	Paris	X8	TGHR	33	23	12	76	19	20

I'd like to use cross-table to create a table that has the following columns:

Region; City; Store; Product; Day; Date; Working Day Pos; Working Day Neg; Quantity (that's the value in the cells).

Thanks for any help.

[zhadrakas]

maybe you are able to do this with the default table wizard:

check this thread for more information

https://community.qlik.com/t5/New-to-QlikView/Inputting-an-excel-table-with-multiple-headers/td-p/15...

regards

tim

[NW1965]

Unfortunately that only provides an answer if I have only one column on the table, when I have multiple column attribute AND multiple header attributes then the only thing that transpose does is switch them around, it doesn't help at all.

[zhadrakas · Accepted solution]

you are right. i thouht there is a way to do it.

Try this script. Worked for me:

SET vSourceFile = 'Crosstable.xlsx'; 
SET vSheet = '[Tabelle1]'; 
SET vType = 'ooxml';

// Put the field names of your horizontal dimensions in the HDims table
// NOTE: Qlikview is case-sensitive, so make sure you don't forget any capitals 
// in your field names.
HDims:
load * inline [
HDimLevel, HFieldName
HDim1, Day
HDim2, Date
HDim3, Working Day+
HDim4, Working Day-
HDim5, Product
];

// Put the field names of your column dimensions in the VDims table
// NOTE: Qlikview is case-sensitive, so make sure you don't forget any capitals 
// in your field names.
VDims:
load * inline [
VFieldName
Region
City
Store
Product
];

// Get the number of horizontal and vertical dimensions
LET vHDims = FieldValueCount('HFieldName');
LET vVDims = FieldValueCount('VFieldName');


// Create vHFieldList as the list of fields of the horizontal dimensions to load 
Temp1:
load concat('@' & RecNo() & ' as ' & HDimLevel ,',') as HFieldList
Resident HDims;

LET vHFieldList = peek('HFieldList');

// Create helper variables for the transformation of the pivot used to create the Levels table
SET vRemoveRows = Remove(Row, Pos(Top, 1));
SET vReplaces = 'Replace(1, top, StrCnd(null))';

for i = 1 to $(vVDims) -1
SET vRemoveRows =$(vRemoveRows),Remove(Row, Pos(Top, 1));
next i

for i = 1 to $(vHDims) -1 
SET vReplaces = $(vReplaces),Replace($(i), top, StrCnd(null)); 
next i

Drop table Temp1;

// Pivot the horizontal dimensions to vertical using the transpose function
// and add a ColNo field so the table can later be associated with the Results table
Levels:
LOAD $(vHFieldList),
rowno()+$(vVDims) as ColNo
FROM $(vSourceFile)
($(vType), no labels, table is $(vSheet), filters(
Transpose(),
$(vRemoveRows),
$(vReplaces)
));

// Load the pivot table with the Crosstable function
LET vHeaders = $(vHDims)-1;
CT:
CrossTable(ValCol, Amount,$(vVDims))
load * from $(vSourceFile)
($(vType), embedded labels, header is $(vHeaders) lines, table is $(vSheet));

// Create vVFieldList to use for comparisons in the creation of the ColNo field
Temp2:
load concat('['&VFieldName&']','&') as VFieldList resident VDims;
LET vVFieldList = peek('VFieldList');
Drop Table Temp2, VDims;

// Add the ColNo field so the table can be associated with the Levels table
ResultTable:
NoConcatenate
load *,
autonumber(RecNo(),$(vVFieldList)) + $(vVDims) as ColNo
Resident CT;

join load * Resident Levels;
drop table Levels;

// Clean up the intermediate CT table
drop Table CT;
drop field ValCol;

// Rename the Levels fields to their proper names
HFieldMap: mapping load * resident HDims;
RENAME Fields using HFieldMap;

Drop Table HDims;

[Brett_Bleess]

Nigel, did Tim's latest post get you what you needed?  If so, do not forget to come back to the thread and use the Accept as Solution button on that post to mark that as correct and to give Tim credit for the help...  If you are still working on things, please leave an update.

Regards,
Brett