How to link 2 Fact tables that share Hierarchy and a key

[dafnis14]

Hi,

After exploring numerous blogs and discussions (like the one below)

https://community.qlik.com/message/547634#547634

I still couldn't figure out a solution for my issue..

I have a balanced  Hierarchy:

Delivery > Offer >Item

One fact table (Deliveries) is link to the highest level (DeliveryID) and one (Customer Returns) to the lowest (Key_Delivery_Offer_Item).

For both I need to sum by the item level which is working fine:

But when I add the customer key, I get a circular reference:

I think Generic Key or concatenation is not a solution because I need all the hiearchy levels for both fact tables.

Any idea how to handle this?

Thanks!

[vinieme12]

Can you post sample excel ?

[dafnis14]

Hi Vineeth,

I added Excel file.

Thank You!

[vinieme12]

Can you explain the relationships please?

Items 1  -  M Delivery

Item 1  - 1 Offer ?   or Item 1 - M Offers ?

What is the relationship between Delivery and Offer?

do you really need DeliveryId in DeliveryOffer table??

[vinieme12]

Will check and get back in sometime

[dafnis14]

Hi,

I attached the flattened hierarchy file of the dimensions as QVD.

I created a similar scenario to the one in reality.

Delivery can have one or more offers.

The same item can be linked to different offers. That's why I created the key: Key_Delivery_Offer_Item.

In the fact table with the customer deliveries I have only DeliveryID.

I don't want to create the items for all deliveries because in the real project it will end up

with tens of millions of records.

Is it clearer now?

Thanks!

[vinieme12]

Check the attached, you can use the Returned flag to see if that delivery was returned.

[dafnis14]

Hi Veneeth,

Thanks for helping!

It's not clear to me how i get the metrics for the delivered items in your solution.

For example, customer 111 delivered items not returned are:

In your solution, it's not clear how i can see them:

Thanks for your patience 🙂

[dafnis14]

In my model, I added a field:

1 as Qty_Delivered

in the Qty Delivered metric I wrote:

Sum(aggr(sum( Qty_Delivered),Delivery_Name,Offer_Name, Item_Name))

[vinieme12]

to count the deliveries that are not returned you should be using the flag that has been created

Example

Deliveries that were not returned will be

Count({<ReturnedFlag-={1}>}Distinct DeliveryID)   /// not equal to 1

Deliveries that were returned will be

Count({<ReturnedFlag={1}>}Distinct DeliveryID)     // equal to 1