Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
dafnis14
Contributor III

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
Honored Contributor

Re: Dimensional Hierarchy & different fact levels

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
Contributor

Re: Dimensional Hierarchy & different fact levels

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
Contributor III

Re: Dimensional Hierarchy & different fact levels

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
Honored Contributor

Re: Dimensional Hierarchy & different fact levels

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
Contributor III

Re: Dimensional Hierarchy & different fact levels

Hi Andrew,

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

Maybe I misunderstood something :-(

Thanks..

dafnis14
Contributor III

Re: Dimensional Hierarchy & different fact levels

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
Honored Contributor

Re: Dimensional Hierarchy & different fact levels

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
Contributor III

Re: Dimensional Hierarchy & different fact levels

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
Honored Contributor

Re: Dimensional Hierarchy & different fact levels

Hi Dafnis,

Good of you to mark your own comment as helpful!

Community Browser