topic Re: Removing synthetic Key from the Data model below in App Development

Removing synthetic Key from the Data model below

Wed, 27 Jan 2016 15:42:51 GMT

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?

Re: Removing synthetic Key from the Data model below

Oleg_Troyansky — Wed, 27 Jan 2016 16:24:34 GMT

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!

Re: Removing synthetic Key from the Data model below

Gysbert_Wassenaar — Wed, 27 Jan 2016 16:37:01 GMT

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.

Re: Removing synthetic Key from the Data model below

Wed, 27 Jan 2016 17:08:41 GMT

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/

Re: Removing synthetic Key from the Data model below

Gysbert_Wassenaar — Wed, 27 Jan 2016 17:45:23 GMT

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

Re: Removing synthetic Key from the Data model below

Oleg_Troyansky — Wed, 27 Jan 2016 18:10:37 GMT

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

Re: Removing synthetic Key from the Data model below

Gysbert_Wassenaar — Wed, 27 Jan 2016 18:49:13 GMT

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.