Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm trying to get next content read from Excel spreadsheet into the Qlikview Cloud:
The point here is I would like to have two different logic tables, or at least one with a new field let's say "Source" which would have two values 'Rent' and 'Empty' for every row in the table according with what's shown in the picture.
The first problem I've faced is Qlikview treats it's column separately, deleting every empty cell, so try to imagin this picture without the empty cells and you'll finde out how format is changing if your trying to retrieve every value with the "fieldvalue" statement.
My second problem goes with the value treatment inside qlikview. In the picture, ID=3 goes in both Rent and Empty "chapters" of the table. But trying to get the second 3 with fieldvalue(Column, Index), it's not getting the '3' but the '5' at that index cause the 3 it's alredy written in the "cloud" of Qlikview.
As far as I have not much experience reading Excel files in Qlikview, I'm open to every suggestion. Maybe there's a better way to do the reading. Any idea of how to solve this?
I'm also publishing the code I've been developing and attaching the original Excel file.
Thanks for your time and atenttion.
Kind regards,
Aitor.
The Qlikview script code:
Directory;
Input:
LOAD @1,
@2,
@3,
@4,
@5,
@6,
@7,
@8,
@9,
@10,
@11,
@12,
@13,
@14
FROM
[..\pruebas.xls]
(biff, no labels, table is Sheet1$);
for i=$(Rent)+2 to $(Empty)-1;
Rent:
Load
Num#(fieldvalue('@1',$(i)),'#.#') as ID,
fieldvalue('@2',$(i)-1) as Desc,
Num#(fieldvalue('@3',$(i)-1),'#.#') as January,
Num#(fieldvalue('@4',$(i)-1) ,'#.#')as February,
Num#(fieldvalue('@5',$(i)-1),'#.#') as March,
Num#(fieldvalue('@6',$(i)-1),'#.#') as April,
Num#(fieldvalue('@7',$(i)-1),'#.#') as May,
Num#(fieldvalue('@8',$(i)-1),'#.#') as June,
Num#(fieldvalue('@9',$(i)-1),'#.#') as July,
Num#(fieldvalue('@10',$(i)-1),'#.#') as August,
Num#(fieldvalue('@11',$(i)-1),'#.#') as September,
Num#(fieldvalue('@12',$(i)-1),'#.#') as October,
Num#(fieldvalue('@13',$(i)-1),'#.#') as November,
Num#(fieldvalue('@14',$(i)-1),'#.#') as December,
'Rent' as Origen
resident Input;
NEXT;
for i=$(Empty) to NoOfRows('Input')-2;
Empty:
Load
Num#(fieldvalue('@1',$(i)+1),'#.#') as ID,
fieldvalue('@2',$(i)-3) as Desc,
Num#(fieldvalue('@3',$(i)-3),'#.#') as January,
Num#(fieldvalue('@4',$(i)-3) ,'#.#')as February,
Num#(fieldvalue('@5',$(i)-3),'#.#') as March,
Num#(fieldvalue('@6',$(i)-3),'#.#') as April,
Num#(fieldvalue('@7',$(i)-3),'#.#') as May,
Num#(fieldvalue('@8',$(i)-3),'#.#') as June,
Num#(fieldvalue('@9',$(i)-3),'#.#') as July,
Num#(fieldvalue('@10',$(i)-3),'#.#') as August,
Num#(fieldvalue('@11',$(i)-3),'#.#') as September,
Num#(fieldvalue('@12',$(i)-3),'#.#') as October,
Num#(fieldvalue('@13',$(i)-3),'#.#') as November,
Num#(fieldvalue('@14',$(i)-3),'#.#') as December,
'Empty' as Origen
resident Input;
NEXT;
Hay posibilidad que uses un formato como el que adjunto?
Si podes usar un formato asi, es mucho mas facil la carga del excel con una tabla cruzada
Hola Federico,
en principio no tengo opción a transformar la Excel de ninguna manera.
Con el código que dejaba puesto, me he acercado bastante a la solución final. Mi mayor problema es que entendiendo mínimamente cómo almacena Qlikview los datos en su... "nube", no sea capaz de recuperar esa segunda aparición del 3 durante la lectura desde RAM.
Gracias por tu respuesta, si consigo modificar el formato de entrada utilizaré tu sugerencia.
Un saludo.
==================================================================================
Hi Federico,
as far as I know, I have no chance to transform the format of the excel document I'm reading.
The scripting code I've published get's me pretty close to the solution, but my problem still is that understanding how Qlikview stores data in it's... "cloud", I'm not able to retrieve that second instance of the '3' value that comes in the file, reading from RAM.
Thanks for your answer, if I'm noticed that I can change the incoming format, I'll use your suggestion.
Kind regards.
El problema es que fieldvalue parece funcionar por columna, no por tabla.
El valor 3 fue cargado en la posicion 5 de tu columna @1.
Me imagino que cuando busca la posicion 9 de tu columna @1 encuentra el 6 porque el 3 ya fue cargado y debe eliminar todos los repetidos (en este caso 3 y ID).
Por esto creo que tampoco cuadran exactamente los valores de los meses.