topic Re: Synthetic Keys problem in QlikView

Synthetic Keys problem

Wed, 24 Sep 2014 11:15:44 GMT

Hi,

i've a strange situation please see attachment. All table present in attachment are Transaction table (Fact Table). In this case i've to resolve synthetic keys? I can't join any table because different granularity. I can concatenate only Article_Promotion and Article_Discount, but this do not resolve any synthetic keys... i cannot concatenate the others tables.

I know that exist link table, but with this solution i just replay the same solution that qlik suggest me with the syntethic keys..

This situation (with synthetic keys) can create data inconsistency?

Please help me thank you.

Re: Synthetic Keys problem

senpradip007 — Wed, 24 Sep 2014 11:18:56 GMT

Can you upload your app?

Re: Synthetic Keys problem

richard_pearce6 — Wed, 24 Sep 2014 11:21:47 GMT

Synthetic keys don't create data inconsitency although they're not great from memory consumption. Having multiple tables joined is not good for memory either.

You can stack these tables by concatenating them without issue. Just do that.

Re: Synthetic Keys problem

Anonymous — Wed, 24 Sep 2014 11:22:49 GMT

you need at least two new keys

Transaction_ID & Hash_Code_Article as %Key1

and

Transaction_ID & Menu_ID as %Key2

furthermore you need to rename Transaction_ID and Hash_Code_Article and Menu_ID

or qualify them

Re: Synthetic Keys problem

Wed, 24 Sep 2014 11:24:44 GMT

Hi Paola Panizza,

You can rename the fields to remove the synthetic keys,if at all both the keys are necessary then you have to create a concatenation of those 2 fields.

Example:: Rename HASH_CODE_Article or concatenate TRANSACTION_ID,HASH_CODE_Article

Re: Synthetic Keys problem

Colin-Albert — Wed, 24 Sep 2014 11:26:03 GMT

You need to concatenate your fields to create a single field to join tables.

Add an "Exit Script;" lone to your load script to just load the first 2 tables, resolve the synthetic keys (if any) the n move the exit script to add another table and so on. Resolving the synthetic keys table by table is much easier than trying to work out the whole data model at once.

e.g. Add a new field TRANSACTION_ID & HASH_CODE_Article as HASH_KEY to Article table and ArticleDiscount tables and remove the TRANSACTION_ID & HASH_CODE_Article fields from ArticleDiscount.

Follow this logic for the other keys.

Re: Synthetic Keys problem

sathishkumar_go — Wed, 24 Sep 2014 11:26:29 GMT

HI,

Try this

ARTICLE DISCOUNT & ARTICLE PROMOTION Table:
1) CREATE A KEY FIELD TRANSACTION_ID & HASH_CODE_ARTICE AS KEY_ARTICLE_DIS_PRO
2) RENAME THE FIELD
TRANSACTION ID

HASC_CODE_ARTICLE

Menu or Article table
3)rename the field Menu_id

-Sathish

Re: Re: Synthetic Keys problem

Wed, 24 Sep 2014 12:11:06 GMT

Re: Re: Synthetic Keys problem

Wed, 24 Sep 2014 12:18:15 GMT

Hi, i cannot rename any field. I need these fields with these association.

Re: Re: Synthetic Keys problem

Colin-Albert — Wed, 24 Sep 2014 12:33:59 GMT

You need to rename the fields to remove the synthetic key.

The association is done on the new key field which concatenates the multiple values that cause the synthetic key.

Try it.

Re: Re: Synthetic Keys problem

sathishkumar_go — Wed, 24 Sep 2014 12:39:33 GMT

You have to rename the field otherwise we can't avoid the synthetic keys.

Don't worry about association. Becuase while you creating the key file will associative.

-Sathish

Re: Re: Synthetic Keys problem

Wed, 24 Sep 2014 13:43:07 GMT

ok, i understand. Only one doubt: in your example:

e.g. Add a new field TRANSACTION_ID & HASH_CODE_Article as HASH_KEY to Article table and ArticleDiscount tables and remove the TRANSACTION_ID & HASH_CODE_Article fields from ArticleDiscount.

do you suggest me to remove the TRANSACTION_ID & HASH_CODE_Article fields from ArticleDiscount and not from Article table, because ArticleDiscount is a subset of Article? Right? In this case all HASH_CODE_Article from ArticleDiscount table are a subset of all HASH_CODE_Article from Article table. In qlik HASH_CODE_Article from Article table has SUBSETRATIO = 100%, on the contrary HASH_CODE_Article from ArticleDiscount has a SUBSETRATIO < 100%

Right? So i need only to pay attention on wich table i drop the field?

thanks!

Re: Re: Re: Synthetic Keys problem

Wed, 24 Sep 2014 14:04:00 GMT

I adjust and fix the syn keys. See attachment, it is correct?

Best regards

Re: Re: Synthetic Keys problem

Colin-Albert — Wed, 24 Sep 2014 14:19:35 GMT

Yes, keep the two fields in that master table as well as the concatenated key, and just add the concatenated key to the other table.

Try adding the tables one at a time in your load script and resolving any synthetic keys table by table, it will make the task much easier.