Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Below link may help you ...
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
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....
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