Load Columns of Excel files by variable

falko_thom — Tue, 23 Feb 2016 14:08:08 GMT

Hello there,

I got an Excel file that has only 8 lines of rows but quite some columns with information which I desire to load.

Underneath these first 8 lines there are more which I do not need. Here are the first few lines and columns:

KW01/2016										KW02/2016										KW03/2016
Gesamt	Wertung	U	K	BR	PR1	SON	Fei	MAK	Flex o.W.	Gesamt	Wertung	U	K	BR	PR1	SON	Fei	MAK	Flex o.W.	Gesamt	Wertung	U	K	BR	PR1	SON	Fei	MAK	Flex o.W.
Mo	15,8	3,0	1,0	0,0	0,0	0,3	0,0	11,5	0,0	Mo	15,8	0,0	2,7	0,0	0,0	0,1	0,0	13,0	0,0	Mo	15,8	1,0	1,0	0,0	0,0	0,1	0,0	13,7	0,0
Di	16,8	1,0	1,0	0,0	0,0	1,8	0,0	13,0	0,0	Di	16,8	0,0	1,7	0,0	0,0	0,0	0,0	15,1	0,0	Di	16,8	1,0	1,9	0,0	0,0	0,3	0,0	13,6	0,0
Mi	15,1	1,0	1,0	0,0	0,0	0,2	0,0	12,9	0,0	Mi	15,1	0,0	1,7	0,0	0,0	0,1	0,0	13,3	0,0	Mi	15,1	1,0	1,9	0,0	0,0	0,8	0,0	11,4	0,0
Do	16,5	2,0	3,4	0,0	0,0	0,1	0,0	11,0	0,0	Do	16,5	0,0	2,1	0,0	0,0	0,2	0,0	14,2	0,0	Do	16,5	1,0	1,9	0,0	0,0	0,2	0,0	13,4	0,0
Fr	15,8	1,0	3,0	0,0	0,0	0,2	0,0	11,6	0,0	Fr	15,8	0,0	1,7	0,0	0,0	0,2	0,0	13,9	0,0	Fr	15,8	1,0	1,9	0,0	0,0	0,2	0,0	12,7	0,0
MAK	80,0	8,0	9,4	0,0	0,0	2,6	0,0	60,0	0,0	MAK	80,0	0,0	9,9	0,0	0,0	0,6	0,0	69,5	0,0	MAK	80,0	5,0	8,6	0,0	0,0	1,6	0,0	64,8	0,0

Each group of these columns start with the weekname in the top left corner. The startin column follow this mathematic rule: weekno-1*10+weekno. Is is possible to tell qlikview to load the following columns using this formula?

Maybe something like

for weekno = 1 to week(today()-1)

let weekstart=weekno-1*10+weekno;

LOAD

'@' & $(weekstart) as Weekday,

'@' & $(weekstart)+1 as Wertung,

'@' & $(weekstart)+2 as U,

...

from Excelfile;

next;

Re: Load Columns of Excel files by variable

MarcoWedel — Tue, 23 Feb 2016 23:46:32 GMT

Hallo Falko,

maybe one solution could be:

tabTemp:
CrossTable (ColNum,ColVal)
LOAD RecNo() as ID, * 
FROM QlikCommunity_Thread_206681.xlsx (ooxml, no labels, table is Tabelle1, filters(Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 1))))
Where RecNo()<=6;    
Join
LOAD '@'&RecNo() as ColNum,
     @1 as Week, 
     @2 as ColName
FROM QlikCommunity_Thread_206681.xlsx (ooxml, no labels, table is Tabelle1, filters(Transpose(),Replace(1, top, StrCnd(null))));

tabResult:
Generic
LOAD ID,
     Week,
     ColName,
     ColVal
Resident tabTemp;

DROP Table tabTemp;

hope this helps

regards

Marco

topic Load Columns of Excel files by variable in QlikView

Load Columns of Excel files by variable

Re: Load Columns of Excel files by variable