Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
------------------------------------------------------------------------------------------------------------------------
Hi, Marcos
Maybe recno() will work for you.
From the help:
Returns an integer for the number of the currently read row of the input table. The first record is number 1.
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
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.
Hi, Marcos
Maybe recno() will work for you.
From the help:
Returns an integer for the number of the currently read row of the input table. The first record is number 1.
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
Works Fine Thank you so much!!
Works Fine Thank you so much!!