2 Replies Latest reply: May 9, 2013 9:11 AM by Gysbert Wassenaar RSS

    Inserting a new Column into transformed excel Data

      I currently have an excel file with 4 tables that each need to be inserted into qlikview.  The problem is that each table represents a different category.

       

      Below is a snippet of the data:

      Electric Consumption (kWh)JanFebMarApr
      Space Cool261368450551
      Heat Reject.1784165718651869
      Refrigeration0000
      Space Heat0000
      Gas Consumption (kBtu)JanFebMarApr
      Space Cool0000
      Heat Reject.121040
      Refrigeration0000
      Space Heat6441
      Electric Demand (kW)JanFebMarApr
      Space Cool1.0610.8741.3011.35
      Heat Reject.2.7512.6992.9532.953
      Refrigeration0000
      Space Heat0000

       


      I realize I need to use the cross table function to get this table into a more friendly qlikview format.  But before I do that, the problem is, how do i add a column to each "set" of data to differentiate what "Type" of data it is eg. Electric Consumption vs Gas Consumption.  I dont think a inline or mapping table will do any good as each Type has the exact same category names (Space Cool, Heat Reject....)

        • Re: Inserting a new Column into transformed excel Data
          Gysbert Wassenaar

          //Load the separate tables into a table named Temp

          Temp:

          LOAD

               'Electric Consumption' as Type,

               [Electric Consumption (kWh)] as Measure,

               Jan,

               Feb,

               Mar,

               Apr

          FROM

          comm81719.xlsx

          (ooxml, embedded labels, table is Sheet1);

           

          // Same field names so the data gets concatenated to Temp

          LOAD

               'Gas Consumption' as Type,

               [Gas Consumption (kBtu)]  as Measure,

               Jan,

               Feb,

               Mar,

               Apr

          FROM

          comm81719.xlsx

          (ooxml, embedded labels, table is Sheet2);

           

          // Same field names so the data gets concatenated to Temp

          LOAD

               'Electric Demand' as Type,

               [Electric Demand (kW)]  as Measure,

               Jan,

               Feb,

               Mar,

               Apr

          FROM

          comm81719.xlsx

          (ooxml, embedded labels, table is Sheet3);

           

          // Apply the crosstable function to table Temp

          Result:

          CrossTable(Month,Value,2)

          load * Resident Temp;

           

          // Temp is no longer needed so drop it 

          drop table Temp;

           

          See attached example.