Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dafnis14
Specialist
Specialist

Dimensional Hierarchy & different fact levels

Hi,

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.

Data_Model.png

I would like to concatenate the 2 fact tables but i receive a synthetic key.

Syntetic_Key.png

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.

Thanks!

11 Replies
effinty2112
Master
Master

Hi Dafnis,

try:

Left Join(Return_Items) // to associate Customer_Deliveries dimensional table with records in Return_Items

LOAD

Distinct

Key_Delivery_Offer_Item,

DeliveryID

Resident Delivery_Offer_Item;

Concatenate(Delivery_Offer_Item)

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.

Left Join(Delivery_Offer_Item)

LOAD * Resident Return_Items;

DROP Table Return_Items;

Good luck

Andrew

mayuresh_d
Partner - Creator
Partner - Creator

bring deliveryId in return_item

return_item:

load *,

Key_Delivery_Offer_Item

from

..................;

left join

load distinct

Key_Delivery_Offer_Item,

DeliveryID,

from

...................Delivery_Offer_Item.qvd;

thn

concatenate return Item and delivery_offer_item table.

and then deliveryId from customer deliveries will automatically associate.

see if it works for you

thanks.

dafnis14
Specialist
Specialist
Author

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?

Thank you!

effinty2112
Master
Master

Hi Dafnis,

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.

Cheers

Andrew

dafnis14
Specialist
Specialist
Author

Hi Andrew,

This brings me back to the original issue of the synthetic key..

Maybe I misunderstood something 😞

Thanks..

dafnis14
Specialist
Specialist
Author

Hi Mayuresh,

I tried what you offered... This brings me back to the original issue of the synthetic key..

Maybe I misunderstood something 😞

Thanks..

effinty2112
Master
Master

Hi Dafnis,

Left Join(Return_Items)

LOAD

Distinct

Key_Delivery_Offer_Item,

DeliveryID

Resident Delivery_Offer_Item;

Concatenate(Delivery_Offer_Item)

LOAD * Resident Return_Items;

DROP Table Return_Items;

gives this data model with no synthetic key:

Concatenation.jpg

Cheers

Andrew

dafnis14
Specialist
Specialist
Author

Hi Andrew,

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

thanks!

effinty2112
Master
Master

Hi Dafnis,

Good of you to mark your own comment as helpful!