3 Replies Latest reply: Feb 21, 2012 11:56 AM by Federico Levi RSS

    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;

        • 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

            • 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.