What is Synthetic key. And how to avoid it?

arethaking — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I got an error message to re-lode my tables in Qlikview file.

please help

Re: What is Synthetic key. And how to avoid it?

Anonymous — Wed, 01 Aug 2018 12:55:26 GMT

Hi Aretha ,

Follow this link , it has detailed explanation of Synthetic keys and how to handle them .

Hope this helps.

Regards

Re: What is Synthetic key. And how to avoid it?

Anonymous — Wed, 01 Aug 2018 12:55:46 GMT

forgot to attach the link .. here it is https://www.analyticsvidhya.com/blog/2014/11/synthetic-keys-qlikview-simplified/

Re: What is Synthetic key. And how to avoid it?

Anil_Babu_Samineni — Wed, 01 Aug 2018 12:57:15 GMT

Question is not full filled?

Re: What is Synthetic key. And how to avoid it?

arvind1494 — Wed, 01 Aug 2018 12:59:06 GMT

You can find more info about Synthetic keys in the QlikView manual.

The concept is, when you join two tables, it can be done based on primary key and foreign key relationship based on a particular column. We can treat it as a normal join between the two tables. The problem starts when the joining is on more than one columns.

The synthetic key scenario is when you have same column name for more than one columns between the loaded tables (e.g. C1 & C2 is common between Table 1 & Table 2). QlikView internally relates those tables based on the common column names and create another internal table (synthetic table) with the matching columns and a synthetic key generated by all possible combined values of the matching columns (C1, C2 and Synthetic Key column....starts with '$'). The impact could be a datamodel with unncessery jumble as well as wastage of memory. e.g. think of two tables with millions of rows. If they have synthetic keys between them, that would generate another table, with large number of rows...and completely unnecessery...finally, eating up memory. And a 'not so well' designed datamodel can of course lead to performance impact in terms of time, as you know.

Now, the question is, if you really want to get rid of the issue, how to fix it.

1. Using QUALIFY

This enforces QlikView to qualify all/selected fields. Qualify implements the check on full path of the field (e.g. Field is designated by Tablename.FieldName).

e.g.

Qualify *;
turns qualification on for all field names of the subsequent table(s).

Qualify “*_Name”;
turns qualification on for all field names ending with _Name in the ssubsequent table(s).

Qualify “Name_*”;
turns qualification on for all field names Starting with Name_ in the ssubsequent table(s).

Qualify “Amt*”,Profit;
turns qualification on for Profit and all field names starting with Amt.

Qualify S???;
turns qualification on for four character field names starting with S.

Ofcourse you don't always need to qualify ALL the fields of a table. E.g. you have C1 & C2, two common columns between Table 1 & Table 2. C1 is used to join the 2 tables and C2 is the column that you think might create Syn Key. You will do something like:

Qualify *;

Unqualify C1;

Select * from Table1;

Select * from Table2;

This will ensure that only C1 is used to join the two tables, eventually nullifying the possibility of Syn Key.

2. Autonumber

If you need to concat the Syn Key candidate fields and create your own key, you can use Autonumber/Autonumberhash128/Autonumberhash256 function. This will create a unique integer value for each distinct combination of the concateneted columns. Autonumberhash128 and Autonumberhash256 creates 128bit and 256bit values respectively.

E.g. In the previous example, autonumber(C1&C2) or autonumberhash128(C1,C2) or autonumberhash256(C1,C2) will create the necessery uniqueness.

One word of cauton using Autonumber is, as they are system generated, you do not have any control over the values. And for external QVDs, since the range of unique autonumber values are limited, uniqueness is not guranted. the hash128 and hash256 functions particularly address this issue and widens the range to ensure uniqueness.

3. Aliasing

If you alias the comflicting fields, i.e. the Syn Key candidate fields, they would be treated as different fields and will not be joined automatically by QV. This is a simple solution based on how flexible the requirements are, in renaming the fields.

Ofcourse you will find many other examples in this forum/QV manual that will strenthen your concept. This is my understanding from my experience with QV. Please let me know if it answers your question on my post.

Re: What is Synthetic key. And how to avoid it?

BalaBhaskar_Qlik — Wed, 01 Aug 2018 15:29:05 GMT

Share your sample script, then it's possible to give an idea on that.

Re: What is Synthetic key. And how to avoid it?

qlikviewwizard — Fri, 03 Aug 2018 04:17:15 GMT

Hi,

If you get any error, please provide screen or sample data.

topic Re: What is Synthetic key. And how to avoid it? in QlikView

What is Synthetic key. And how to avoid it?

Re: What is Synthetic key. And how to avoid it?

Re: What is Synthetic key. And how to avoid it?

Re: What is Synthetic key. And how to avoid it?

Re: What is Synthetic key. And how to avoid it?

Re: What is Synthetic key. And how to avoid it?

Re: What is Synthetic key. And how to avoid it?