How to use Join tables then concatenate them into 1 table

[#1]

Hello,

my objective is too have 1 and only 1 large fact table in my qvd.

Right now, I have 5 tables: 4 facts tables (sales, stock, orders, delivery) with 1 key (different from each table) and 1 product master.

My idea is to:

1) create first the 4 facts tables with product info ==> Using JOIN in load

2) Then concatenate the 4 tables into only 1 "data" table

I succeed to do 1), but how can I do 2) ?

Here is my load statement that achieve 1):

STOCK:

LOAD key1,

     qty

FROM stock.xlsx

(ooxml, embedded labels, table is Sheet1)

WHERE qty <> 0;

LEFT JOIN (STOCK)

LOAD key1,

    key2,

    key3,

    key4,

    dimA

    dimB,

    dimC,

    dimD,

    dimE,

    dimF

FROM master.xls

(biff, embedded labels, table is Sheet1);

SALES:

LOAD key2,

     [Sold Qty]

FROM sales.xlsx

(ooxml, embedded labels, table is Sheet1)

WHERE [Sold Qty] <> 0;

LEFT JOIN (SALES)

LOAD key1,

    key2,

    key3,

    key4,

    dimA

    dimB,

    dimC,

    dimD,

    dimE,

    dimF

FROM master.xls

(biff, embedded labels, table is Sheet1);

DELIVERY:

LOAD key3,

     [Estimated Arrival Date],

     [Delivered Qty] as "In Transit Qty"

FROM delivery.xls

(biff, embedded labels, table is Sheet1);

LEFT JOIN (DELIVERY)

LOAD key1,

    key2,

    key3,

    key4,

    dimA

    dimB,

    dimC,

    dimD,

    dimE,

    dimF

FROM master.xls

(biff, embedded labels, table is Sheet1);

ORDER:

LOAD key4,

     [Launch Date]

FROM order.xlsx

(ooxml, embedded labels, table is Sheet1);

LEFT JOIN (LAUNCH)

LOAD key1,

    key2,

    key3,

    key4,

    dimA

    dimB,

    dimC,

    dimD,

    dimE,

    dimF

FROM master.xls

(biff, embedded labels, table is Sheet1);

Thank you

[#2]

Below link may help you ...

http://community.qlik.com/groups/qvscriptor-from-schema-to-script/blog/2011/11/11/understanding-join...

[sbaldwin]

Hi, you need to reload the 4 tables you have created, something like:

data:

load * resident STOCK;

concatenate(data)

load * resident SALES;

concatenate(data)

load * resident DELIVERY;

concatenate(data)

load * resident ORDER;

drop tables STOCK,SALES,DELIVERY,ORDER;

Thanks

Steve

[#4]

Hello,

thanks for your detailed answer, however I now got an error during the load with following message:

Table not found

CONCATENATE (DATA)

LOAD * RESIDENT SALES

and same message for other tables (stock, delivery, order)

I checked on the community and it seems I need to use the NOCONCATENATE, but my goal is to concatenate them into one table, so I'm still very confused....

[#5]

If I add the NOCONCATENATE flag right after the table name (the one to consolidate all data), I don't have the error anymore.

DATA:

NOCONCATENATE

LOAD * RESIDENT STOCK;

CONCATENATE (DATA)

LOAD * RESIDENT SALES;

CONCATENATE (DATA)

LOAD * RESIDENT DELIVERY;

CONCATENATE (DATA)

LOAD * RESIDENT ORDER;

DROP TABLES STOCK,SALES,DELIVERY,ORDER;

However, When I look at the data source, then all my dimension fields are empty... it's like the previous JOIN statements where "forgotten". If I take back my example, DimA, DimB, DimC etc. are empty:

DimA	DimB	DimC	Key1	Key2	Measure1	Measure2
-	-	-	123		10	0
-	-	-	456		15	0
-	-	-	-	abc	0	30
-	-	-	-	def	0	49

What I need is:

DimA	DimB	DimC	Key1	Key2	Measure1	Measure2
UK	blue	right	123		10	0
FR	red	right	456		15	0
US	red	left		abc	0	30
DE	green	top		def	0	49

Thank you