Synthetic tables data loading

[#1]

how data is loaded when there is a synthetic table in qlikview?

[pokassov]

Hello!

You have at least 2 equal fields in at least 2 tables.

Try "table viewer" - > "Source Table View" and you will see which fields and which tables.

[alexandros17]

You have syntethic Keys when tables are linked with more than one field,

if you have a low number of synt Keys with a few fields in the key you have no problem, at the opposite (high number of synt Keys) performances decrease (you can join or concatenate tables as an alternative) or avoid synt. key

[#4]

Qlikview joins all the tables those are having same field name.

For example: you have 2 tables T1, T2 and all 2 are having column EmployeeID, DepartmentID (Exactly same name) then all 2 tables will get joined automatically. Now You want to Join only T1 and T2 based on EmployeeID and DepartmentID. then What qlikview will do is create $Syn table with an extra column $Syn1 (combination of EmployeeID, DepartmentID) and  $Syn1 will be added in T1 and T2.

To handle this- You should created a 3rd column (EMP_DEPT_ID) in T1 and T2 which is combination of EmployeeID, DepartmentID and you need to change the name of EmployeeID, DepartmentID in either T1 or T2.

So finally T1 and T2 will join based on one column EMP_DEPT_ID.

[qlikviewwizard]

Hi rambabu23885,

What you want to know exactly?

Synthetic keys won't give wrong results. But it impact the performance.

Read HIC's  Synthetic Keys ,HOW TO REMOVE SYNTHETIC KEYS‌,How to remove Synthetic Keys?

Hope it will helps you.

[#6]

When any two table share more than one common field qilkview automatically create synthetic key. By doing this qlikview try to associate both table through the combination of all the common fields between them. As a outcome of this additional table will create with all the shared fields and additional key field, this key field added to all tables involved earlier and linked with it.

As a best practice of successful data modeling elimination of synthetic tables is required and there are few ways to do that.

Hope this will help for you.

[rwunderlich]

"Synthetic keys won't give wrong results."

I respectfully disagree, Syn Keys frequently -- not always, but frequently -- represent a data quality problem or trap. They are generally worth removing for quality reasons. Some are legit and ok to leave, but I would examine and make a decision on each one.

-Rob

[Anonymous]

Hi Rambabu,

Synthetic Keys:

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.

If you remove synthetic keys from your datamodel

1. Less confusing data model (U'll not get confused with data model)

2. Potential performance improvement when loading records into QV Server (computer's) RAM and shorter CPU processing

3. Potential removal of erroneous aggregations (I.e. "bad calculations")

* In some situations, Synthetic keys are not always "bad". Simply depends on the data modeling situation in combination with desired performance requirements (or expectations).

[Anonymous]

Please go through this link for clear and better understanding  about Synthetic Keys or Tables with Good Example:

http://www.analyticsvidhya.com/blog/2014/11/synthetic-keys-qlikview-simplified/

[qlikviewwizard]

qlikviewnovice

Good info.