Synthetic Keys in QlikView and how & when to avoid them?

[#1]

With practical example.

Thanks&Regards

Ramesh

[#2]

Hi,

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

See this i think it ll be helpful for u to understand about synthetic keys.

[#3]

Hi,

Check this out and find the attached qvw file

1. Script with Syn Key

LOAD CustId,

    Name,

    Address,

    Contact

FROM

(ooxml, embedded labels, table is Cust);

LOAD DepID,

    Name,

    Address,

    CustId

FROM

(ooxml, embedded labels, table is Dep);

2. Script with out syn Key

scenario 1:

LOAD CustId,

    Name,

    Address,

    Contact

FROM

(ooxml, embedded labels, table is Cust);

LOAD DepID,

    Name as Dep_Name,

    Address as Dep_Address,

    CustId

FROM

(ooxml, embedded labels, table is Dep);

scenario 2:

QUALIFY *;

UNQUALIFY CustId;

UNQUALIFY DepID;

LOAD CustId,

    Name,

    Address,

    Contact

FROM

(ooxml, embedded labels, table is Cust);

LOAD DepID,

    Name,

    Address,

    CustId

FROM

(ooxml, embedded labels, table is Dep);

[MarcoWedel]

Another interesting post by John Witherspoon (http://community.qlik.com/people/JohnW)

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

regards

Marco

[#5]

Synthetic keys are nothing but some common fields between tables.It should be avoided at the time of Data Modelling because tables should be combined by a Primary key which is more convenient.To avoid we can alias the common fields by using Qualify or doing it manually.

Thanks

[vardhancse]

if there are more than 1 common fields in tables, then those fields will be called as synthetic keys.

These can be rectified in many ways:

1.     Rename of the fields

2.     removing same fields which are not required(Remove duplicate fields)

[siva_boggarapu]

Hi,

we can remove Synthetic keys by using

Qulify and Unqulify statements.

For more help Press "F1" in Qlikview tool.

Thanks,

Siva