Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Data Model

How to get rid of Synthetic keys  in the data model ? ....

4 Replies
aadilmadarveet
Valued Contributor

Re: Data Model

use a link table.. or change the field names that are not required to be linked.. or create a composite key..

Edit.. Check this.. http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

MVP
MVP

Re: Data Model

Link your tables by only 1 common field name.

Create a combined key if you need two or more fields to be involved or just leave the Syn key as is.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

Not applicable

Re: Data Model

You get synthetic keys when several fields of 1 table are linked to these fields in another table. So,

1) try to reduce the common fields : if you have date, month, year in the fact table, you could remove month and year from it : just the date is enough to do the link with a table composed of date, month, year ....

2) create your own keys

a) you can concatenate them

Field1 & '|' & Field2 & '|' ... as UniqueKey in your fact table

Field1 & '|' & Field2 & '|' ... as UniqueKey, Field1, Field2 ... in your link table

b) use the AutonumberHashXXX that will create a unique integer for each couple given in argument

AutonumberHashXXX(Field1, Field2, Field3 ...) as uniqueKey in Fact Table

AutonumberHashXXX(Field1, Field2, Field3 ...) as uniqueKey, Field1, Field2, Field3 ..; in the link table

But I encourage you to read these two posts by Johk Witherspoon and Henric Cronström:

http://community.qlik.com/message/10279#10279

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

Fabrice

ngulliver
Valued Contributor III

Re: Data Model

Hi, Anitha.

Have a look at the blog:

http://www.quickintelligence.co.uk/perfect-your-qlikview-data-model/

this discusses the use of synthetic keys and best ways to optimize your data model.

Cheers,

Neil

Community Browser