Can I merge or concatenate two separate crosstables????

[#1]

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

[Oleg_Troyansky]

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!

[#3]

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)

[Oleg_Troyansky]

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;

[#5]

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;