topic Re: Data modelling avoiding synthetic keys in QlikView

Data modelling avoiding synthetic keys

Sat, 14 May 2016 15:52:31 GMT

Hi,

I am used to database programs like MySQL now I am into Qlikview and learned about synthetic keys.

Is it always bad behavior if you have them? I know the standard things like renaming, qualify using aggregate keys.

I try to avoid synthetic keys. Synthetic keys get created when more then 1 field matches other tables.

Since Qlikview wants to join based on equal field names like shopID, retourNr and Count.

Count must not joined at all I could qualify this one. But how to avoid synthetic key of ShopID. If I qualify ShopID as well I loose my link with master data table. People must be able to select data based on ShopID.

My data model is like this:

BRS:

RetourNr,

Type ('def', 'mas' or 'inc')

ShopID,

Count

...

ShopScanned:

RetourNr

ShopID

Count

As masterdata I have:

ShopID, with detailed info of the shop.

So BRS --> ShopScanned -> Shop.

One solution is to create a field ScanShopID in master data table Shop and use this in ShopScanned as well this creates a loop but in practice not since only records with type 'mas' are using the ScanShopID

Thanks for helping me.

John

Re: Data modelling avoiding synthetic keys

Sat, 14 May 2016 16:42:21 GMT

Hi Jhon ,

In your datamodel you need the combination of those two fields(RetourNr,ShopID) to create an a true ID field right?

What's the problem when you try to concatenate them, hope can help you.

Re: Data modelling avoiding synthetic keys

Anonymous — Sat, 14 May 2016 16:56:06 GMT

You could either concatenate or join your BRS and ShopScanned tables. I usually prefer concatenate but without knowing your data I cannot suggest definitively, but I reckon concatenate would be the way to go.

I do not know your full data model, but this will go towards creating a Star Schema which is the most recommended model to go for.

Whilst synthetic keys are often bad, they are not always bad. The one created by the IntervalMatch() is one of the few good ones. But in general synthetic keys are a sign of either sloppy modelling of sloppy scripting.

Re: Data modelling avoiding synthetic keys

Sat, 14 May 2016 18:19:05 GMT

Thanks. I played with it for hours changing it to a join.

But one table blows up everything and script not loaded.

All fields are different and retournr is the key item. Strange I agree.

Is there a way to do a join after loading the data and combine it in a new table?

John

Re: Data modelling avoiding synthetic keys

Sat, 14 May 2016 19:05:59 GMT

I solved it by using store and concatenate.

John

Re: Data modelling avoiding synthetic keys

swuehl — Sat, 14 May 2016 22:46:02 GMT

If your request is solved, please close this thread by flagging answers as helpful and / or correct (or setting the thread to assumed answered)

Qlik Community Tip: Marking Replies as Correct or Helpful

And just in case you haven't already seen this blog post

Synthetic Keys

Re: Data modelling avoiding synthetic keys

Anonymous — Sun, 15 May 2016 05:32:40 GMT

it is useful to you

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