Synthetic keys

[kushalthakral]

Dear All

i just wanted know all about Synthetic keys

like what are synthetic keys

whats their impact

how to remove them

Thanks

Kushal Thakral

[its_anandrjs]

Synthetic key generates when multiple table has same names and or avoiding rename keys or you can use composite keys.

See the example of synthetic keys

Tab1:

LOAD * Inline

[

Field1,Field2,Name

1,2,John

];

Tab2:

Load * Inline

[

Field1,Field2,Address

1,3,ddd

];

When you read this table a synthetic table is created like attached snap

To remove make composite key combinations

And for avoiding synthetic keys make composite keys like

Tab1:

LOAD Field1&'_'&Field2 as Key,Name;

LOAD * Inline

[

Field1,Field2,Name

1,2,John

];

Tab2:

LOAD Field1&'_'&Field2 as Key,Address;

Load * Inline

[

Field1,Field2,Address

1,3,ddd

];

[#3]

Please see this

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

It is advisable to replace Synth keys with proper primary key joins

[Luis_Cortizo]

Hi,

   I believe you will find this links very useful:

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

http://www.learnallbi.com/qlikview-resolving-synthetic-keys/

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

https://www.youtube.com/watch?v=FYTSqLwG50M

Hope it helps.

Best regards.

[cfz]

Hi kushal,

I would start here to learn about synthetic keys:

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

I hope it helps.

Carlos

[its_anandrjs]

See the example of synthetic keys

Tab1:

LOAD * Inline

[

Field1,Field2,Name

1,2,John

];

Tab2:

Load * Inline

[

Field1,Field2,Address

1,3,ddd

];

[kushalthakral]

Hi Anand

Since you are combining Field1 and Field 2 to make it as Key

But what if i need to use Field1 and Field2 also as a separate to do any calculation

Thanks

Kushal

[MK_QSL]

When more than one field of any tables are having same name, QlikView script will generate a Synthetic Key...

Synthetic Key (sometimes!) creates a performance issue and not always but as a good data model, developers like to remove Synthetic Key

There are few tricks and tips to remove Synthetic Key

1) Remove unwanted fields having same names...

    Like, if you have Date and Month fields common in two tables, you can remove Month from any table

2) Rename fields

    Like you have address field common in Customer and Supplier Tables

    as we know that there is no relation between Customer and Supplier address we can rename them as

    Address as Supplier_Address

    and

    Address as Customer_Address

3) Use Qualify and UnQualify method to rename the field

4) In case, you can't use any of above 3, you can remove the Synthetic Keys by

         A) Concatenating the two tables

         B) Create a Link Table which contains all common field responsible for Synthetic Key

It is difficult to provide examples of all above points but you can get them by searching on QlikView Community ..!

Hope this helps .....

[its_anandrjs]

You can rename them like for another use

Tab1:

LOAD Field1&'_'&Field2 as Key,Name,Field1 as Table1_Field1, Field2 as Table1_Field2;

LOAD * Inline

[

Field1,Field2,Name

1,2,John

];

Tab2:

LOAD Field1&'_'&Field2 as Key,Address,Field1 as Table2_Field1,Field2 as Table2_Field2;

Load * Inline

[

Field1,Field2,Address

1,3,ddd

];

[kushalthakral]

Hi All

i have structure like below and each table has month and year which needs to be consider in calculation