Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
NW1965
Creator
Creator

Excel with multiple header lines

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:

   DayMonTueWedThuFriMon
   Date04.11.1905.11.1906.11.1907.11.1908.11.1911.11.19
   Working Day+WD+1WD+2WD+3WD+4WD+5WD+6
   Working Day-WD-21WD-20WD-19WD-18WD-17WD-16
RegionCityStoreProduct      
EMEALondonX1ABC123424221819
EMEAParisX8TGHR332312761920

 

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.

 

Labels (1)
4 Replies
zhadrakas
Specialist II
Specialist II

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
Creator
Creator
Author

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
Specialist II
Specialist II

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
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.