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

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

Mon, 21 Nov 2011 15:54:45 GMT

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);

STORE Otros_Indicadores INTO Otros_Indicadores.qvd;

DROP TABLE Cabeceras;

DROP TABLE tmpHojas;

EXIT SCRIPT;

DROP TABLE Otros_Indicadores;

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

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

Mon, 21 Nov 2011 16:05:26 GMT

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;

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

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

erichshiino — Mon, 21 Nov 2011 16:12:52 GMT

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

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

Mon, 21 Nov 2011 16:26:36 GMT

Works Fine Thank you so much!!

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

Mon, 21 Nov 2011 16:26:51 GMT

Works Fine Thank you so much!!