Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to use Join tables then concatenate them into 1 table

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

4 Replies
Not applicable

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

sbaldwin
Contributor III

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

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

Not applicable

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

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

Not applicable

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

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:

DimADimBDimCKey1Key2Measure1Measure2
---123100
---456150
----abc030
----def049

What I need is:

DimADimBDimCKey1Key2Measure1Measure2
UKblueright123100
FRredright456150
USredleftabc030
DEgreentopdef049

Thank you

Community Browser