topic Re: What is synthetic keys? How it can be avoided? in QlikView

What is synthetic keys? How it can be avoided?

Tue, 17 Jan 2012 09:23:55 GMT

Hi All,

What is synthetic keys? How it can be avoided?

Thanks,

Attitude

What is synthetic keys? How it can be avoided?

Tue, 17 Jan 2012 09:35:14 GMT

Hi,

Please go through Page 473 of QlikView Reference Manaual. It will give your fair idea about Synthetic key and it can be avoided in two ways.

1. Use Qualify

2. Use Alias

Cheers,

What is synthetic keys? How it can be avoided?

SunilChauhan — Tue, 17 Jan 2012 09:38:19 GMT

if more than one column linked between two tables its create sythetic key.

and you remove it using qualify/unqualify.

qualify all the fields

and unqulify the key field.

synthetic key creates synthetic tables in Contol +T(table viewer)

sythetic table tells you how many fields creates synthic key

hope this help

What is synthetic keys? How it can be avoided?

m_woolf — Tue, 17 Jan 2012 12:24:43 GMT

You might want to read a thread titled "Should we stop worrying and love the synthetic key?" by John Witherspoon

Re: What is synthetic keys? How it can be avoided?

Tue, 09 Apr 2013 16:54:53 GMT

Hi At tude,

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 keys? How it can be avoided?

Gabriel — Thu, 11 Apr 2013 15:08:43 GMT

Hi,

Synthetic Keys

When two or more input tables have two or more fields in common, this

implies a composite key relationship. QlikView handles this through synthetic

keys. These keys are anonymous fields that represent all occurring

combinations of the composite key. When the number of composite keys

increases, depending on data amounts, table structure and other factors,

QlikView may or may not handle them gracefully. QlikView may end up

using excessive amount of time and/or memory. Unfortunately the actual

limitations are virtually impossible to predict, which leaves only trial and

error as a practical method to determine them.

I think the best way to avoid Synthetic Keys is to (a) Rename the fields, (b) if you don't need the one of the field you delete it from your script.

Best Regards,

Gabriel

Re: What is synthetic keys? How it can be avoided?

Anonymous — Thu, 11 Apr 2013 15:49:14 GMT

By using qualify key word or by changing the field name

Re: What is synthetic keys? How it can be avoided?

Fri, 08 Aug 2014 08:24:57 GMT

Hi,

When we load data into qlikview there could be occasions where the fields of two tables may be the same name. The qlikview creates associations when loading the data to make it easier to manipulate but it could cause a problem when there are more than one pair of matching fields in two tables.

Solution: As the guys have mentioned above, you should rename it using either alias or qualify.

If you use the alias you can give a field name of your choosing eg: DeliveryDate as OrderDeliveryDate.

If you use the qualify keyword it would append the table name and then the field name - <table_name>.fieldname.

As there is no chance of tables with similar names, the qualify keyword will be a preferred choice if there are may fields to be changed. However if there is only a handful of fields, then you can use the field aliasing.

Hope this helps!

Re: What is synthetic keys? How it can be avoided?

Tue, 28 Apr 2015 08:34:27 GMT

Synthetic keys is a Complex Keys consisting of two or more fields connecting tables, Resource Heavy, May slow down calculations, Make a document harder to understand and maintain and Should be removed if possible.

How to Removed Synthetic keys:

- Renaming a fileds use( As) ex: Customer as %Customer

-Adding a Remarks or comment out unneccesary fields ex: // Customer,

-Concatenating fields

Re: What is synthetic keys? How it can be avoided?

vishsaggi — Tue, 28 Apr 2015 08:37:19 GMT

Hello,

Go through the below link you will get more insights on the raised topic.

Synthetic keys: to have or not to have ?

Thanks,

Re: What is synthetic keys? How it can be avoided?

Anonymous — Wed, 21 Dec 2016 07:21:25 GMT

Hello,

It's the way Qlikview joins 2 tables with more than one columns named the same. Qlikview creates a temporary table and calls this a 'Synthetic Key.

There are various ways to avoid the synthetic keys in Qlikview as follows.

1)renaming the fields

2) commenting the fields.

3)joining

4)concatenate

5)complex keys

6)link table

7)qualify.

find the attachment,it may help you to understand.

R/Murali

Re: What is synthetic keys? How it can be avoided?

varshavig12 — Wed, 21 Dec 2016 08:46:18 GMT

Synthetic Keys

Re: What is synthetic keys? How it can be avoided?

vardhancse — Wed, 21 Dec 2016 08:48:17 GMT

QlikView adds synthetic table (as $Syn table) andsynthetic key (as $Syn symbol) to the data model. The keys are added to the user uploaded tables and are used to join with synthetic table. Synthetic key isQlikView's method to deal with composite keys.

Re: What is synthetic keys? How it can be avoided?

Wed, 21 Dec 2016 10:57:19 GMT

hi,

whenever two/more tables have field names in common, then qlikview automatically creates a table with those field names, which are nothing but synthetic keys. To avoid it, just rename the field names

Re: What is synthetic keys? How it can be avoided?

muthukumar77 — Wed, 21 Dec 2016 11:38:49 GMT

Hi,

You can use,

1. Link Key

2. Concatenate

3. Joins

Refer: https://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/

Re: What is synthetic keys? How it can be avoided?

Wed, 21 Dec 2016 22:50:28 GMT

•When there is more than one field in common between tables, is called Synthetic key.

4 ways to remove synthetic key

1. using JOIN between tables

2.create CONCATENATED KEY

3.create CONCATENATED TABLE

4.create LINK TABLE

Re: What is synthetic keys? How it can be avoided?

praveenkumar_s — Fri, 06 Jan 2017 09:33:39 GMT

Hi,

When two table has more than one field in common ,it forms synthetic key...

Use alias name ..its easy way to remove synthetic key..

eg: consider EmployeeID is forming synthetic key between two tables means alias the EmployeeID using AS EMPLOYEEID..this will remove synthetic key.

Re: What is synthetic keys? How it can be avoided?

alis2063 — Sat, 07 Jan 2017 11:39:45 GMT

What are Synthetic Keys or Tables?

When we load two tables with a common field name in both tables, the tables get associated automatically based on the common fields. This is the associative functionality of QlikView.

However, when we have more than one common field between two or more tables, QlikView creates “SYNTHETIC KEYS” and “SYNTHETIC TABLE”. QlikView adds synthetic table (as $Syn table) and synthetic key (as $Syn symbol) to the data model. The keys are added to the user uploaded tables and are used to join with synthetic table.

Synthetic key is QlikView’s method to deal with composite keys. The Synthetic table contains a synthetic key that is a composite of all the combinations of the multiple key fields connecting the tables. In our data model, we had two fields common “Branch” and “Region” so QlikView created synthetic key and synthetic table on its own. If we look at the source table view of table viewer, it shows that two fields are connected between these two tables.

How to remove Synthetic keys?

To remove synthetic keys, we first look at our data model and make necessary changes, if required. We have multiple methods to remove synthetic keys but it depends upon the requirement

Removing fields: When common fields causing synthetic keys are not required in data model and doing so will not affect the relationship between two tables. Removing fields can be done by commenting or removing field from load script.

Renaming fields (Using QUALIFY): When common fields causing synthetic keys are not same field (not containing similar values), These are actually different fields with same name. Renaming can be done by using “AS” clause. We can also achieve this by using QUALIFY statement. With qualified statement, fields names are converted in the “TableName.FieldName” format

Using QUALIFY

Autonumber/ Composite Keys: When we know common fields causing synthetic keys are important for data model then we can create our own key to handle composite keys. We can also use Autonumber / Autonumberhash128 / Autonumberhash256 functions to create composite keys. These will create a unique bit value for each distinct combination of the concatenated columns. Autonumberhash128 and Autonumberhash256 creates 128bit and 256bit values respectively. Please note Autonumber may be problematic in applications generating the QVD files for use in other QlikView applications.
We also have other methods to remove synthetic keys like “Creating a link table“, “Creating Explicit Joins ” and “Concatenating similar tables“. These topics must be discussed in detail and will explore it in future.

Re: What is synthetic keys? How it can be avoided?

Sat, 07 Jan 2017 11:53:56 GMT

When any two tables share more than one common field, QlikView creates a complex key, or synthetic key, to try and associate both tables through the combination of all of the common fields between them. This takes the form of an additional table containing the shared fields and an additional key field added to all involved tables.

Because of QlikView's associative engine, the two tables are automatically linked through both fields, creating a complex key out of the combination of their values. There is also a third table in our data model, called $Syn 1 Table. This is the synthetic table which stores the combination of values for the two fields which, as pointed out, form the synthetic key. The presence of synthetic keys in a data model can cause the application to have slow response time and sometimes even consume all available resources. Therefore, they need to be avoided when possible. There are several methods we can use to remove synthetic keys: • We can rename those fields that are a part of the synthetic key but should not be a part of the association between the two tables. • We can remove conflicting fields from one of the two tables. To remove a field, we just erase the corresponding line of code from the Load script. • We can create an explicit complex key with the concatenation of all common fields that actually represent the link between the two tables. ° After creating the new complex key, we can remove the conflicting fields from either table.

Re: What is synthetic keys? How it can be avoided?

beck_bakytbek — Sun, 08 Jan 2017 21:31:16 GMT

Hi titude,

you can avoid them by:

1. qualify / unqualify

2. creating of komposit key,

3. creating of linking tables

4. korrekt renaming of fields

but., it is very importing to know and understand own business environment, if you know well your environment, so you will avoid the creating of synthetic keys

beck