Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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