Discussion Board for collaboration on QlikView Scripting.
In the attached model I have a dimensional hierarchy in a flat table (Delivery_Offer_Item).
Delivery > Offer > Item
the 2 facts tables are linked to it with different fields and to different levels.
The request is to be able to display a delivered or returned item per customer.
I would like to concatenate the 2 fact tables but i receive a synthetic key.
My question is which is the right way to handle it.
Should I outer join the items in the dimension to Customer_Deliveries ?
In reality, the The Customer Deliveries have tens of millions of records of deliveries only.
I would appreciate very much your help here.
Left Join(Return_Items) // to associate Customer_Deliveries dimensional table with records in Return_Items
LOAD * Resident Return_Items;
DROP Table Return_Items;
In this concatenated table you will lose associations between fields that you have in the original data model. If you really want to combine into one table maybe a join would work better.
bring deliveryId in return_item
concatenate return Item and delivery_offer_item table.
and then deliveryId from customer deliveries will automatically associate.
see if it works for you
Hi Andrew & Mayuresh,
From what you both suggest, I understand that it is not possible to maintain the dimension table as it is.
I don't understand why..
Could you please elaborate?
Why isn't it possible to concatenate the fact tables?
You can concatenate the fact tables instead of joining. As Mayuresh says at the end of his post "see if it works for you".
Try concatenation and try joining, do both and compare results when making selections.
This brings me back to the original issue of the synthetic key..
Maybe I misunderstood something :-(
I tried what you offered... This brings me back to the original issue of the synthetic key..
gives this data model with no synthetic key:
Yes, that's a compromise, because the dimension table turns into a fact table..
If there would be no other solution I wii go for it ..
Good of you to mark your own comment as helpful!