Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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....)
//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.
Thanks Gysbert,
But I've still got the personal edition of qlikview. Any way to better elaborate what your process was?