thanks for your detailed answer, however I now got an error during the load with following message:
Table not found
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.
LOAD * RESIDENT STOCK;
LOAD * RESIDENT SALES;
LOAD * RESIDENT DELIVERY;
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