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

[kushalthakral]

[its_anandrjs]

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.

[#4]

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);

[kushalthakral]

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

[its_anandrjs]

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

];

[kushalthakral]

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