Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

falko_thom
New 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

Re: Load Columns of Excel files by variable

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