Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Not applicable

Synthetic Keys problem

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.

Tags (4)
1 Solution

Accepted Solutions
Highlighted

Re: Re: Synthetic Keys problem

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.

View solution in original post

13 Replies
Highlighted
Valued Contributor III

Re: Synthetic Keys problem

Can you upload your app?

Highlighted
Luminary
Luminary

Re: Synthetic Keys problem

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.

Highlighted
Partner
Partner

Re: Synthetic Keys problem

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

Highlighted
Not applicable

Re: Synthetic Keys problem

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

Highlighted

Re: Synthetic Keys problem

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.

Highlighted
Partner
Partner

Re: Synthetic Keys problem

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

Highlighted
Not applicable

Re: Re: Synthetic Keys problem

!

Highlighted
Not applicable

Re: Re: Synthetic Keys problem

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

Highlighted

Re: Re: Synthetic Keys problem

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.