Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have created to separate crosstables and I need to merge the two tables.
BenchMarkTable:
CrossTable(Measure,Value,3)
LOAD
text(ITEM) as ITEM,
FYEAR,
FPERIOD,
sum(NO_UNITS) as [Shipment Qty],
sum(SALES_AMNT) as [Gross Sales],
sum(REV_TOTAL) as [Net Revenue]
Resident sales_master
GROUP BY ITEM, FYEAR, FPERIOD;
benchmark_override_table:
CrossTable(Measure, Value_Ovr,3)
LOAD
ITEM,
FYEAR,
FPERIOD,
sum(SHIP_QTY_OVR) as [Shipment Qty],
sum(GROSS_SALES_OVR) as [Gross Sales],
sum(NET_REV_OVR) as [Net Revenue]
Resident bench_override
GROUP BY ITEM, FYEAR, FPERIOD;
So what I would hope to see once the tables have merged is Item, fyear, fperiod, Measure, Value, and Value_Ovr
can this be done & if so, how????
I tried concatenation but I received an error
crosstable loads are quirky, they don't allow many features like preceding loads, concatenated loads etc...
Simply load two tables separately and then reload one of them using a RESIDENT load, and concatenate it into another. Drop the unnecessary table afterwards.
cheers,
Oleg Troyansky
www.masterssummit.com - take your QlikView skills to the next level!
Oleg,
I'm a newbie but would I have to do something like this:
BenchMarkTable:
CrossTable(Measure,Value,3)
LOAD
text(ITEM) as ITEM,
FYEAR,
FPERIOD,
sum(NO_UNITS) as [Shipment Qty],
sum(SALES_AMNT) as [Gross Sales],
sum(REV_TOTAL) as [Net Revenue]
Resident sales_master
GROUP BY ITEM, FYEAR, FPERIOD;
benchmark:
Load * resident BenchMarkTable;
benchmark_override_table:
concatenate(benchmark)
CrossTable(Measure, Value_Ovr,3)
LOAD
ITEM,
FYEAR,
FPERIOD,
sum(SHIP_QTY_OVR) as [Shipment Qty],
sum(GROSS_SALES_OVR) as [Gross Sales],
sum(NET_REV_OVR) as [Net Revenue]
Resident bench_override
GROUP BY ITEM, FYEAR, FPERIOD;
I tried this but got an error message:
Table not found
benchmark_override_table:
Concatenate(benchmark)
CrossTable(Measure, Value_Ovr,3)
wait a minute, I think your problems begin way before concatenating... I doubt if crosstable load can allow a group by aggregation. I think you need to separate the two loads. Schematically, it should look like this:
// 1. Load the first crosstable
BM_Temp:
CrossTable(Measure,Value,3)
LOAD
...
resident
...
;
// 2. Aggregate the data from the first crosstable
BM:
LOAD
text(ITEM) as ITEM,
FYEAR,
FPERIOD,
sum(NO_UNITS) as [Shipment Qty],
sum(SALES_AMNT) as [Gross Sales],
sum(REV_TOTAL) as [Net Revenue]
Resident
BM_Temp
GROUP BY
ITEM, FYEAR, FPERIOD
;
// 3. Load the second crosstable:
BM_Override_Temp:
BM_Temp:
CrossTable(Measure,Value,3)
LOAD
...
resident
...
;
// 4. aggregate the data from the second crosstable and concatenate it to the previously loaded table.
concatenate (BM)
LOAD
ITEM,
FYEAR,
FPERIOD,
sum(SHIP_QTY_OVR) as [Shipment Qty],
sum(GROSS_SALES_OVR) as [Gross Sales],
sum(NET_REV_OVR) as [Net Revenue]
Resident
BM_Override_Temp
GROUP BY
ITEM, FYEAR, FPERIOD;
// 5. Drop temp tables
drop table BM_Temp, BM_Override_Temp;
Try this type of scripting.
BenchMarkTable:
CrossTable(Measure,Value,3)
LOAD
text(ITEM) as ITEM,
FYEAR,
FPERIOD,
sum(NO_UNITS) as [Shipment Qty],
sum(SALES_AMNT) as [Gross Sales],
sum(REV_TOTAL) as [Net Revenue]
Resident sales_master
GROUP BY ITEM, FYEAR, FPERIOD;
Concatenate(BenchMarkTable)
CrossTable(Measure,Value_Ovr,3)
LOAD
ITEM,
FYEAR,
FPERIOD,
sum(SHIP_QTY_OVR) as [Shipment Qty],
sum(GROSS_SALES_OVR) as [Gross Sales],
sum(NET_REV_OVR) as [Net Revenue]
Resident bench_override
GROUP BY ITEM, FYEAR, FPERIOD;