topic Re: Inserting a new Column into transformed excel Data in QlikView

Inserting a new Column into transformed excel Data

Thu, 09 May 2013 12:38:32 GMT

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)	Jan	Feb	Mar	Apr
Space Cool	261	368	450	551
Heat Reject.	1784	1657	1865	1869
Refrigeration	0	0	0	0
Space Heat	0	0	0	0
Gas Consumption (kBtu)	Jan	Feb	Mar	Apr
Space Cool	0	0	0	0
Heat Reject.	12	10	4	0
Refrigeration	0	0	0	0
Space Heat	6	4	4	1
Electric Demand (kW)	Jan	Feb	Mar	Apr
Space Cool	1.061	0.874	1.301	1.35
Heat Reject.	2.751	2.699	2.953	2.953
Refrigeration	0	0	0	0
Space Heat	0	0	0	0

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 — Thu, 09 May 2013 12:51:47 GMT

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

Re: Inserting a new Column into transformed excel Data

Thu, 09 May 2013 13:06:55 GMT

Thanks Gysbert,

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