3 Replies Latest reply: Mar 6, 2012 6:24 AM by Erich Shiino RSS

    Load excel data in custom format

    Muralidhar Koti

      Hi,

       

      Attached is a excel sheet where business stores data in a custom format. I have a requirement to load this data into QlikView and create dashboards.

       

      I need data to be transformed into the below format

       

      Row ID, Insurer, Rate, Line

       

      Also business would be adding new insurers at the right (columns) and the script should load them atumatically without any script change.

       

      Could you please help me with the script?

       

      Regards,

      Murali

        • Load excel data in custom format
          Jason Michaelides

          Hi,

           

          Are you able to change the header of this spreadsheet? Can you make the header into a single row and have:

          RowID

          Insurer1Rate

          Insurer1Line

          Insurer2Rate

          Insurer2Line

          Insurer3Rate

          Insurer3Line

          etc...

           

          If you can do this then the Crosstable() function plus a little jiggery-pokery will achieve what you want.

           

          Jason

            • Load excel data in custom format
              Muralidhar Koti

              Unfortunately NO.

               

              This file is generated by an automated process and chaging this means a change to the current system which is generating the file. I don't think client will agree for the same.

               

              Are there any other ways to achieve this?

                • Re: Load excel data in custom format
                  Erich Shiino

                  Hi,

                  You need some transformation:

                  During the Wizard to load the table, enable transformation step, rotate the type -> Transpose, then use fill on the first column.

                  Then you use cross table.

                   

                  After this, I believe you would be able to use the data but to make exactly in your format I used load resident and an outer join.

                   

                  I just had to adjust the cell F1 of your example, because there was a second value there 'Ascot'. I don't know why.

                   

                  Hope this helps,

                   

                  Regards,

                   

                  Erich