How to avoid synthetic key??

[#1]

Hi,

am new to the QlikView..........

Please can any one let me know how to avoid Synthetic key with small example........

Regards,

Hanumanth Reddy.Y

[its_anandrjs · Accepted solution]

Suppose you have two tables Region sales and region discount and table Region sales contains by Region and Country and table 2 contains Region,Country,Discount and discount by Region & Country if you load this table a synthetic table is created between two tables with key

EG:-

RegionSales:

Load * Inline

[

Region,Country,Sales

Europe,Austria,2344

Africa,Angola,1290

North America,Canada,568

];

RegionDiscount:

Load * Inline

[

Region,Country,Discount

Europe,Austria,120

Africa,Angola,80

North America,Canada,58

];

So for removing synthetic keys you can rename the keys other  wise check the model and make joins between them other wise leave it with association with key Region&Country as Key.

Eg:-

RegionSales:

LOAD Region&Country as Key,Sales;

load * Inline

[

Region,Country,Sales

Europe,Austria,2344

Africa,Angola,1290

North America,Canada,568

];

RegionDiscount:

LOAD Region&Country as Key,Discount;

load * Inline

[

Region,Country,Discount

Europe,Austria,120

Africa,Angola,80

North America,Canada,58

];

Explaination:-

1. In the above example there is other possibility to use the tables like if there is same fields then concatenate the 2 tables.

2. You can join this two tables with one unique keys.

3. You can create a link table with this tables and maintain the link table.

4. As you see there is two common fields then you can make a composite keys and link 2 tables.

Read a document what is synthetic keys What is Synthetic Key?

Thanks & Regards

[PrashantSangle]

Hi,

Basically there are various way to avoid synthetic key

1.> Rename the column

2. If both table contain same structure then simply concatenate both tables,

3. If more than two fields are required to create association then create Composite Key to solve the Synthetic key.

But if you think synthetic key make bad association then read this article.

For more details read article written by Henric Cronström

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

4.You can also avoid synthetic key by creating key table

for more details read

Strategy for creating Key Tables

5.By joining two tables you can also avoid synthetic key

Regards,

PS

[mambi]

Hi,

you can use QUALIFY , UNQUALIFY or rename your keys

[#5]

Use more Concatenation function in Script.

[nizamsha]

any one of them u can use according to ur situation

1.Qualify *,UnQualify

2,Use alias like Sales as Sales1

3,Comment the unwanted fileld in the table that is giving synthectic Key if u dont want

4,Concatenate the table

[#7]

Thank you most usefull!!!!!!!