1 Reply Latest reply: Feb 23, 2016 6:49 PM by Marco Wedel RSS

    Load Columns of Excel files by variable

    Falko Thom

      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;

        • Re: Load Columns of Excel files by variable
          Marco Wedel

          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