Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
falko_thom
Contributor III
Contributor III

Load Columns of Excel files by variable

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/2016KW02/2016KW03/2016
GesamtWertungUKBRPR1SONFeiMAKFlex o.W.GesamtWertungUKBRPR1SONFeiMAKFlex o.W.GesamtWertungUKBRPR1SONFeiMAKFlex o.W.
Mo15,83,01,00,00,00,30,011,50,0Mo15,80,02,70,00,00,10,013,00,0Mo15,81,01,00,00,00,10,013,70,0
Di16,81,01,00,00,01,80,013,00,0Di16,80,01,70,00,00,00,015,10,0Di16,81,01,90,00,00,30,013,60,0
Mi15,11,01,00,00,00,20,012,90,0Mi15,10,01,70,00,00,10,013,30,0Mi15,11,01,90,00,00,80,011,40,0
Do16,52,03,40,00,00,10,011,00,0Do16,50,02,10,00,00,20,014,20,0Do16,51,01,90,00,00,20,013,40,0
Fr15,81,03,00,00,00,20,011,60,0Fr15,80,01,70,00,00,20,013,90,0Fr15,81,01,90,00,00,20,012,70,0
MAK80,08,09,40,00,02,60,060,00,0MAK80,00,09,90,00,00,60,069,50,0MAK80,05,08,60,00,01,60,064,80,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;

1 Reply
MarcoWedel

Hallo Falko,

maybe one solution could be:

QlikCommunity_Thread_206681_Pic1.JPG

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