topic Re: How to use Join tables then concatenate them into 1 table in QlikView

How to use Join tables then concatenate them into 1 table

Tue, 11 Feb 2014 08:41:12 GMT

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

Re: How to use Join tables then concatenate them into 1 table

Tue, 11 Feb 2014 08:47:56 GMT

Below link may help you ...

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

Re: How to use Join tables then concatenate them into 1 table

sbaldwin — Tue, 11 Feb 2014 08:51:30 GMT

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

Re: How to use Join tables then concatenate them into 1 table

Wed, 12 Feb 2014 01:39:42 GMT

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

Re: How to use Join tables then concatenate them into 1 table

Wed, 12 Feb 2014 01:57:54 GMT

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