Removing synthetic Key from the Data model below

[#1]

I ave attached a screenshot of my current data model.

I'm not entirely sure how I would get rid of the synthetic keys here. Can anyone provide a good solution?

[Oleg_Troyansky]

Hi Asma,

I can't explain everything in a single community post, but I can recommend to you my new book QlikView Your Business. In the book, I explain all the data modelling techniques that are required here (and a lot more), and I even describe the analytical solution that's very similar to yours - Inventory Analysis.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

[Gysbert_Wassenaar]

There are several possible solutions. If the sales/purchase transactions are not directly related to inventory transaction you could simply concatenate the two tables and add a field for the transaction type. If a sales/purchase transaction must be directly related to inventory transactions you can create a link table.

1. Create a field in both fact tables: autonumber(TransactionDate & '|' & masterid & '|'& warehouse) as LinkKey.

2. Create the link table

LinkTable:

LOAD DISTINCT LinkKey, TransactionDate, masterid, warehouse FROM DailyInventory;

CONCATENTAT(LinkTable)

LOAD DISTINCT LinkKey, TransactionDate, masterid, warehouse FROM SalesAndPurchases;

3. Drop the TransactionDate, masterid and warehouse fields from the two fact tables:

4. Once you understand how and why this works use the Qlik Components Library to create that necessary link table for you.

[#4]

Thanks Gysbert. That is really helpful. However, I do not fully understand how that will work. Within the SalesandPurchases Table, there may be duplicate records. Meaning there may be two purchase/sale records for the same Item from the same Warehouse on the same date. In that case this would not account for that.

Here is the link that I was looking at:

http://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/

[Gysbert_Wassenaar]

No problem. All records will have the same LinkKey so they are associated correctly.

[Oleg_Troyansky]

Hi Gysbert,

let me disagree with you here... This is one of those examples where Link Table wouldn't quite work well and Concatenated Facts would work a lot better.

The main reason for that is the field Transaction Date - in the Inventory table, Transaction Date means the day of the inventory transaction or sometimes the date of the inventory snapshot that was taken. In Sales records, the same field means the date of Sale, while in Purchasing the same field means the date of Purchase.

Linking these facts using the Link Table would lead to wrong associations - the Inventory balances will only link to Sales records if they happened on the same Date. This is unlikely the desired result here.

So, the preferred solution here is the Concatenated Fact. However, to understand all the intricacies of building a proper Concatenated Fact and restoring all broken associations, one needs to learn it methodically, not just read about it on the forum, - hence my recommendation to read it in my book, or to learn advanced Data Modelling with Barry Harmsen at the Masters Summit for Qlik.

cheers,

Oleg Troyansky

[Gysbert_Wassenaar]

Let me disagree with you here... This is one of those examples where Link Table wouldn't quite work well and Concatenated Facts would work a lot better.

I don't think we disagree. I suggested a concatenated fact table as well. But that by itself does not solve the issue of sales, purchase and inventory dates.