topic Re: How to link 2 Fact tables that share Hierarchy and a key in QlikView

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

dafnis14 — Sat, 26 Aug 2017 07:16:43 GMT

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!

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

vinieme12 — Sat, 26 Aug 2017 07:50:59 GMT

Can you post sample excel ?

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

dafnis14 — Sat, 26 Aug 2017 07:55:47 GMT

Hi Vineeth,

I added Excel file.

Thank You!

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

vinieme12 — Sat, 26 Aug 2017 07:58:15 GMT

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

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

vinieme12 — Sat, 26 Aug 2017 07:59:26 GMT

Will check and get back in sometime

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

dafnis14 — Sat, 26 Aug 2017 08:26:21 GMT

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!

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

vinieme12 — Sat, 26 Aug 2017 08:43:58 GMT

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

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

dafnis14 — Sat, 26 Aug 2017 09:11:33 GMT

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 🙂

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

dafnis14 — Sat, 26 Aug 2017 09:14:30 GMT

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

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

vinieme12 — Sat, 26 Aug 2017 09:16:30 GMT

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

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

vinieme12 — Sat, 26 Aug 2017 09:17:15 GMT

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

<<CORRECTION>>

Example

Deliveries that were not returned will be

count({<%KEY_ReturnedDelivery={"=isnull(Returned.ReturnedFlag)"}>}DISTINCT DeliveryID)

Deliveries that were returned will be

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

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

dafnis14 — Sat, 26 Aug 2017 09:26:53 GMT

Unfortunately , it doesn't work...

Count({<Returned.ReturnedFlag-={1}>}Distinct DeliveryID)

The returned quantity is also wrong...

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

vinieme12 — Sat, 26 Aug 2017 09:29:15 GMT

just made a correction above

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

dafnis14 — Sat, 26 Aug 2017 09:31:42 GMT

Not there yet... 😞

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

vinieme12 — Sat, 26 Aug 2017 09:35:50 GMT

The returned quantity is also wrong...

How is it wrong when we are counting Distinct DeliveryID?

and you can see Customer 111 has returned only DeliveryID =1 , so that is correct

if you want to see how many offers then change to Distinct Returned.OfferID or

distinct Returned.ItemId to see how many Items were returned

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

vinieme12 — Sat, 26 Aug 2017 09:36:34 GMT

Sorry; but you have to tell me clearly what you values you expect to see!

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

vinieme12 — Sat, 26 Aug 2017 09:36:55 GMT

The returned quantity is also wrong...

How is it wrong when we are counting Distinct DeliveryID?

and you can see Customer 111 has returned only DeliveryID =1 , so that is correct

if you want to see how many offers then change to Distinct Returned.OfferID or

distinct Returned.ItemId to see how many Items were returned

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

dafnis14 — Sat, 26 Aug 2017 09:44:18 GMT

for customer 111 it shows:

I attached the desired output in the post:

When a customer is selected, it should display the relevant quantities.

This is where I was stuck.. because I Had trouble to add a customer key..

Thanks!

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

dafnis14 — Sat, 26 Aug 2017 10:06:35 GMT

Hi,

I think the issue is because there isn't a record for Delivery ID that has return items, like the ones created for 2 and 3.

That's why I can't count delivered items for delivery 1.

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

vinieme12 — Sat, 26 Aug 2017 10:12:34 GMT

I don't know what you are trying to achieve now, can you tell me exact numbers that you expect to see!!

as i already gave you the expressions to use

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

<<CORRECTION>>

Example

Deliveries that were not returned will be

count({<%KEY_ReturnedDelivery={"=isnull(Returned.ReturnedFlag)"}>}DISTINCT DeliveryID)

Deliveries that were returned will be

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

Can you post what expressions you are using?