topic Re: Synthetic keys in QlikView

Synthetic keys

kushalthakral — Thu, 24 Apr 2014 09:24:18 GMT

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

Re: Synthetic keys

its_anandrjs — Thu, 24 Apr 2014 09:27:11 GMT

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

];

Re: Synthetic keys

Thu, 24 Apr 2014 09:27:57 GMT

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

Re: Synthetic keys

Luis_Cortizo — Thu, 24 Apr 2014 09:28:23 GMT

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.

Re: Synthetic keys

cfz — Thu, 24 Apr 2014 09:28:55 GMT

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

Re: Synthetic keys

its_anandrjs — Thu, 24 Apr 2014 09:32:02 GMT

See the example of synthetic keys

Tab1:

LOAD * Inline

[

Field1,Field2,Name

1,2,John

];

Tab2:

Load * Inline

[

Field1,Field2,Address

1,3,ddd

];

Re: Synthetic keys

kushalthakral — Thu, 24 Apr 2014 09:42:08 GMT

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

Re: Synthetic keys

MK_QSL — Thu, 24 Apr 2014 10:11:16 GMT

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 .....

Re: Synthetic keys

its_anandrjs — Thu, 24 Apr 2014 10:15:50 GMT

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

];

Re: Synthetic keys

kushalthakral — Thu, 24 Apr 2014 10:19:28 GMT

Hi All

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

Re: Synthetic keys

kushalthakral — Thu, 24 Apr 2014 10:20:01 GMT

Re: Synthetic keys

its_anandrjs — Thu, 24 Apr 2014 10:24:09 GMT

Then i suggest make a link tables or you can make composite keys. But if your model has good performance and there is less amount of data then do not make keys and just follow the synthetic tables.

Re: Synthetic keys

Thu, 24 Apr 2014 10:32:10 GMT

Keys between tables that are generated internally by QlikView in cases where there are several keys linking the tables. Synthetic keys are sometimes an indication of a poorly or even incorrectly designed data model.

See the example of synthetic keys

TAB1:

load

Feild1&'_'& Feild2 as ID,

Add

FROM

(ooxml, embedded labels, table is Sheet1);

TAB2:

LOAD Feild1,

Feild2,

Address

FROM

(ooxml, embedded labels, table is Sheet2);

When you read this table a synthetic table is created like attached screen shot.

by using QUALIFY ,UN QUALIFY /Composite key/Alias name To remove synthetic keys.

By using Composite Key:

TAB1:

load

Feild1&'_'& Feild2 as ID,

Add

FROM

(ooxml, embedded labels, table is Sheet1);

TAB2:

LOAD

Feild1&'_'& Feild2 as ID,

Address

FROM

(ooxml, embedded labels, table is Sheet2);

By Using Alias Name:

TAB1:

load

Feild1 as id,

Feild2,

Add

FROM

(ooxml, embedded labels, table is Sheet1);

TAB2:

LOAD Feild1,

Feild2,

Address

FROM

(ooxml, embedded labels, table is Sheet2);

By Using Qualify:

qualify Feild1;

load

Feild2,

Add

FROM

(ooxml, embedded labels, table is Sheet1);

TAB2:

LOAD Feild1,

Feild2,

Address

FROM

(ooxml, embedded labels, table is Sheet2);

Re: Synthetic keys

kushalthakral — Thu, 24 Apr 2014 10:34:25 GMT

Thanks Anand

As of now i have few data but on monthly basis it will increase

so in future it might slow down the performance as i have used complex expressions in it

Can you help me to resolve the same

i have also shared the structure image

and how to create link table or composite key.

Thanks

Kushal

Re: Synthetic keys

its_anandrjs — Thu, 24 Apr 2014 11:31:32 GMT

On the basis of your attached image i provide the sample load script of the model if you load your table like below

MC.9:

LOAD Material,Month as MC.9_Month ,Year as MC.9_Year;

LOAD * Inline

[

Month,Year,Material

1,2,3

];

Model:

LOAD [Where Used],Month as Model_Month,Year as Model_Year;

LOAD * Inline

[

Month,Year,Where Used

1,2,3

];

GL_Group:

LOAD Material,[Where Used], Month as GL_Group_Month, Year as GL_Group_Year;

LOAD * Inline

[

Month,Year,Material,Where Used

1,2,3,4

];

Re: Synthetic keys

kushalthakral — Thu, 24 Apr 2014 11:42:01 GMT

Thanks Anand for the efforts

But i need to use Month as a filter

So if use Month's name different for each table then in that case it is not showing correct results

Thanks

Kushal