Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

2 Replies

Re: Inserting a new Column into transformed excel Data

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


talk is cheap, supply exceeds demand
Not applicable

Re: Inserting a new Column into transformed excel Data

Thanks Gysbert,


But I've still got the personal edition of qlikview.  Any way to better elaborate what your process was?

Community Browser