6 Replies Latest reply: Aug 17, 2012 5:01 AM by blue_1591 RSS

    Load Rows as Columns while importing Excel Report

      Hello Alll,

       

      I am importing a data source from Excel spreadsheet where I have columns and rows as below. I want to convert coloured rows into separate columns (please see attcahed sheet) and load into a table box. I tried to load in the script using Crosstable but it wouln't display columns as I want e.g. Product,  Date, Country, Target, Actuals,f/cast and cum.dev.

      I have also attached the excel sheet of what i'm trying to say.

       

      PLEASE HELP ME ...

       

       

      DateJanFebMarAprMayJunJulAugSepOctNovDecfull year
      Product Atarget1.01.01.01.01.01.01.01.01.01.01.01.012.0
      actuals, f/castChina1.51.51.51.51.51.51.51.51.51.51.51.518.0
      France0.0
      UK0.0
      USA0.0
      cum. dev.-0.5-0.5-0.5-0.5-0.5-0.5
      Product Btarget2.02.02.02.02.02.02.02.02.02.02.02.024.0
      actuals, f/castChina1.51.51.51.51.51.51.51.51.51.51.51.518.0
      France0.0
      UK0.0
      USA1.21.21.21.21.21.21.21.21.21.21.21.214.4
      cum. dev.0.00.00.00.00.80.80.8
      Product Ctarget3.03.03.03.03.03.03.03.03.03.03.03.036.0
      actuals, f/castChina0.0
      France1.01.01.01.01.01.01.01.01.01.01.01.012.0
      UK0.0
      USA1.11.11.11.11.11.11.11.11.11.11.11.113.4
      cum. dev.0.00.00.00.00.90.90.9
        • Re: Load Rows as Columns while importing Excel Report
          Jason Michaelides

          It looks like CrossTable() and a pivot table should achieve this no problem. Can you provide a spreadsheet of sample data as its not clear from the table above?

            • Re: Load Rows as Columns while importing Excel Report

              Thanks for reply Jason. Please see attachments 'Excel_Report_in_QlikView.xlsx'. I have also attcahed qvw file.

               

              Using CrossTable() I loaded file as;

               

              MyTable:
              CrossTable(Date, Data)
              LOAD F1 as Products,
              Date,
              F3,
              Jan,
              Feb,
              Mar,
              Apr,
              May,
              Jun,
              Jul,
              Aug,
              Sep,
              Oct,
              Nov,
              Dec,
              [full year]
              FROM
              Excel_Report_in_QlikView.xlsx
              (ooxml, embedded labels, table is Sheet1);

               

              I want Country, Targets and Data in separate columns..

              Really appreciate your help guys..

                • Re: Load Rows as Columns while importing Excel Report

                  try this code

                   

                  //first load - duplicate missing dimension values (product and country)

                  robo1:

                  LOAD

                       if(isnull(F1),peek(tempProduct),F1) as tempProduct,

                      if(isnull(Date),peek(tempType),Date) as tempType,

                      F3 as tempCountry,

                       Jan,      Feb,      Mar,      Apr,      May,      Jun,      Jul,      Aug,      Sep,      Oct,      Nov,      Dec

                  FROM [C:\Documents and Settings\BOMI\Pulpit\Excel_Report_in_QlikView.xlsx] (ooxml, embedded labels, table is Sheet1);

                   

                  //crossinf prev loaded table

                  robo2:

                  crosstable (month, value, 3)

                  load tempProduct as product,

                      tempType as type,

                      tempCountry as country,

                       Jan,      Feb,      Mar,      Apr,      May,      Jun,      Jul,      Aug,      Sep,      Oct,      Nov,      Dec

                  resident robo1;

                  drop table robo1;

                   

                  //creating final table (each country and product)

                  final:

                  load F1 as Product

                  FROM [C:\Documents and Settings\BOMI\Pulpit\Excel_Report_in_QlikView.xlsx] (ooxml, embedded labels, table is Sheet1) where isnull(F1)=0;

                   

                  left join load distinct F3 as Country

                  FROM [C:\Documents and Settings\BOMI\Pulpit\Excel_Report_in_QlikView.xlsx] (ooxml, embedded labels, table is Sheet1) where isnull(F3)=0;

                   

                  //adding target column

                  left join (final) load product as Product, type, month as Month, value as Target resident robo2 where type='target';

                  drop field type from final;

                   

                  //adding cum. dev. column

                  left join (final) load product as Product, type, month as Month, value as [cum. dev.] resident robo2 where type='cum. dev.';

                  drop field type from final;

                   

                  //adding actuals, f/cast column

                  left join (final) load product as Product, country as Country,  type, month as Month, value as [actuals, f/cast] resident robo2 where type='actuals, f/cast';

                  drop field type from final;