Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Reading Excel: format and repeated values issue

Hi all,

I'm trying to get next content read from Excel spreadsheet into the Qlikview Cloud:

Rents.jpg

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;

Tags (3)
3 Replies
Not applicable

Re: Reading Excel: format and repeated values issue

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

Not applicable

Reading Excel: format and repeated values issue

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.

Not applicable

Re: Reading Excel: format and repeated values issue

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.

Community Browser