4 Replies Latest reply: Nov 21, 2011 11:26 AM by Marcos Herrera RSS

    Where RowNumber() < $(vRows) Problem in load script

      Hi Dear QlikCommunity Friends

       

      I have a Load Sentence Script and aim of this is to read multiple excel sheets in each there a table consisting of a metric name and a value, the number of rows varies between sheets, all leaves starts at the same but ends at different levels and should get to read this to prevent unwanted information

       

       

      I tried with "where RowNo()<$(vFilas);" but does not work well within a For cycle

       

      --------------------------------------------------------

      tmpHojas:

      LOAD * INLINE [

      LlavePresupuesto,Hoja,filas

      CUC-54001,Cucuta,19

      CUC-54405,Patios,19

      CUC-54874,Villa,19

      CUC-85001,Yopal,19

      CUC-20011,Aguachica,19

      MZL-17001,EMASMAN,19

      MZL-17144,EMASCHI,19

      OCC-17042,EMASOCC,19

      CAL-76001,EMASCALI,19

      PAS-52001,EMASPASTO,19

      CGN-13001,AUCOSTA,19

      PAN-507,EMASPAN,19

      SJO-70001,ADESA,9

      ING-17001,TECNIMAN,10

      ING-08001,TECNIBQLLA,10

      ING-76130,TECNICALI,10

      ING-54001,TECNIQQTA,10

      ING-,TECNIBTA,10

      HOL-11001,Holding,3

      ];

      LET vFilastblHojas = NoOfRows('tmpHojas');

       

      Cabeceras:

      LOAD * INLINE [

          Cabecera

          40544

          40575

          40603

          40634

          40664

          40695

          40725

          40756

          40787

          40817

          40848

          40878

       

      ];

      LET vFilastblCabecera = NoOfRows('Cabeceras');

       

       

      FOR vCuentaHojas = 0 to $(vFilastblHojas)-1

      LET vHoja = Peek('Hoja',vCuentaHojas,'tmpHojas');

       

      LET vLlaveLocalidad = chr(39) & Peek('LlavePresupuesto',vCuentaHojas,'tmpHojas') & chr(39);

      FOR i = 0 to $(vFilastblCabecera)-1

      LET vVariable = '[' & Peek('Cabecera',i,'Cabeceras') & ']' ;

      LET vFecha = Peek('Cabecera',i,'Cabeceras');

       

      LET vFilas = Peek('filas',vCuentaHojas,'tmpHojas');

      Otros_Indicadores:

      LOAD  $(vLlaveLocalidad)  as LlaveLocalidad,

           F3 AS Indicador,

           // FieldValue($(vVariable) ,$(vCuenta)) as Valor,

           $(vVariable) as Valor,

           If(Day(Date($(vFecha)))>1,

              Date('01/'&Num(Month(Date(If(Len($(vFecha))>5,Mid($(vFecha),1,5),$(vFecha)))))

                   & '/'&Year(Date(If(Len($(vFecha))>5,Mid($(vFecha),1,5),$(vFecha)))),'DD/MM/YYYY'),

              Date($(vFecha),'DD/MM/YYYY'))  as Fecha

      FROM

      [Archivo general cifras 2011-2012.xlsx]

      (ooxml, embedded labels, header is 87 lines, table is $(vHoja))

      where RowNo()<$(vFilas);

      NEXT

      NEXT

      STORE Otros_Indicadores INTO Otros_Indicadores.qvd;

      DROP TABLE Cabeceras;

      DROP TABLE tmpHojas;

      EXIT SCRIPT;

      DROP TABLE Otros_Indicadores;

      ------------------------------------------------------------------------------------------------------------------------