Data modelling avoiding synthetic keys

[#1]

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

[#2]

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.

[Anonymous]

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.

[#4]

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

[#5]

I solved it by using store and concatenate.

John

[swuehl]

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

[Anonymous]

it is useful to you

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