Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

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;

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

1 Solution

Accepted Solutions
Partner
Partner

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

Hi, Marcos

Maybe recno() will work for you.

From the help:

RecNo( )

Returns an integer for the number of the currently read row of the input table. The first record is number 1.

RowNo( )

Returns an integer for the position of the current row in the resulting QlikView input table. In contrast to RecNo( ), which counts the records in the raw data table, the RowNo( ) function does not count records that are excluded by where clauses and is not reset when a raw data table is concatenated to another. The first row is number 1.

Hope this helps,

Erich

4 Replies
Not applicable

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

I tried this sentence and the same problem happened

For i= 1 to 10

LOAD F3,

     [40575]

FROM

[Archivo general cifras 2011-2012.xlsx]

(ooxml, embedded labels, header is 87 lines, table is Cucuta)

where RowNo()<19;

Next

Just load the first 19 rows when i is =1, but when i is = 2,3,4,...10 just load 1 row.

Partner
Partner

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

Hi, Marcos

Maybe recno() will work for you.

From the help:

RecNo( )

Returns an integer for the number of the currently read row of the input table. The first record is number 1.

RowNo( )

Returns an integer for the position of the current row in the resulting QlikView input table. In contrast to RecNo( ), which counts the records in the raw data table, the RowNo( ) function does not count records that are excluded by where clauses and is not reset when a raw data table is concatenated to another. The first row is number 1.

Hope this helps,

Erich

Not applicable

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

Works Fine Thank you so much!!

Not applicable

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

Works Fine Thank you so much!!