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]

Check the attached

[dafnis14]

Unfortunately something is wrong.

You still miss delivery that has  return items in them delivery 1 for customer 111..)

Your solution:

Mine Only with deliveries:

Is a link table between the 2 fact tables possible?

Thanks again for your efforts!

[vinieme12]

You still miss delivery that has  return items in them delivery 1 for customer 111..)

Which one?

You absolutely cannot have ItemName in the Deliveries table; because you don't have that data!

You only have ItemName for the returned deliveries

[vinieme12]

I don't know how you are evaluating it even after me giving you the expressions to use!!

[dafnis14]

I am definitely aware that I miss the item ID for the deliveries. But I got it counted right without the customers.

I'm referring to your model: you can see that it does not show all items that were delivered.

For customer 111 - delivery ID 1 is not included in the "not returned" Table.

In order to be able to see these missing deliveryIDs

I thought maybe to add the following your script:

Tmp:

LOAD Distinct %KEY_ReturnedDelivery as Tmp_%KEY_ReturnedDelivery

Resident TransactionTable

Where Returned.ReturnedFlag =1;

Concatenate (TransactionTable)

LOAD * Where  Exists(Tmp_%KEY_ReturnedDelivery, %KEY_ReturnedDelivery)

;

LOAD CustomerID,

     DeliveryID,

     DeliveryID as DeliveryIDtoOffer,

     CustomerID&'_'&DeliveryID as %KEY_ReturnedDelivery,

     1 as Flag_AddDeliv

FROM

Items Deliveries.xlsx

(ooxml, embedded labels, table is [Customer Deliveries]);

[vinieme12]

For customer 111 - delivery ID 1 is not included in the "not returned" Table.

Just Check back with your Data to verify

Customer 111 has Delivery id's 1,2 and 3

CustomerID	DeliveryID	City
111	1	NY
111	2	NY
111	3	NY

and in the Returned Delivery Customer 111 has Delivery id 1,

so if Delivery ID 1 has been returned why would it appear in then"NOT RETURNED" table??

CustomerID	DeliveryID	OfferID	ItemID
111	1	1	2
111	1	1	3

[vinieme12]

if you just want a count of Deliveries irrespective of returnedflag just remove the set analysis

Dimension

      CustomerID

Expression

     Count(distinct DeliveryID)

[dafnis14]

Ahh, ok...

We have to know what was delivered - by the item id, per customer

and what was returned - by the by the item id, per customer....

This is in order to be able to analyze it.

In reality, we are talking about sent and redeemed coupons 🙂

Thanks!

[dafnis14]

Hi,

I added to your model a summary table.

The delivered items look fine!

But the returned items are not counted as expected.

Could you please help me figure out why?

Thanks!

[vinieme12]

what expression have you used?