topic Re: Synthetic tables data loading in QlikView

Synthetic tables data loading

Wed, 27 May 2015 07:38:58 GMT

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

Re: Synthetic tables data loading

pokassov — Wed, 27 May 2015 07:51:51 GMT

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.

Re: Synthetic tables data loading

alexandros17 — Wed, 27 May 2015 07:59:11 GMT

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

Re: Synthetic tables data loading

Wed, 01 Jul 2015 00:53:50 GMT

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.

Re: Synthetic tables data loading

qlikviewwizard — Wed, 01 Jul 2015 02:16:07 GMT

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.

Re: Synthetic tables data loading

Wed, 01 Jul 2015 02:24:33 GMT

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.

Re: Synthetic tables data loading

rwunderlich — Wed, 01 Jul 2015 02:33:19 GMT

"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

Re: Synthetic tables data loading

Anonymous — Wed, 01 Jul 2015 02:44:43 GMT

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).

Re: Synthetic tables data loading

Anonymous — Wed, 01 Jul 2015 02:47:29 GMT

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/

Re: Synthetic tables data loading

qlikviewwizard — Wed, 01 Jul 2015 03:21:29 GMT

qlikviewnovice

Good info.