What is synthetic keys? How it can be avoided?

[#1]

Hi All,

What is synthetic keys? How it can be avoided?

Thanks,

Attitude

[Anonymous]

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

[varshavig12]

Synthetic Keys

[vardhancse]

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.

[#5]

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

[muthukumar77]

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/

[#7]

•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

[praveenkumar_s]

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.

[alis2063]

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.

[#10]

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.

[beck_bakytbek]

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