topic Synthetic key fields in QlikView

Synthetic key fields

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

Dear all,

I am using 2 tables where there's the description of product codes (Table: PRODUIT and Table SOUS_PRODUIT).

QLikview is generating a loop when I use the key fields CODEMP and CODESMP but not CODPROD and CODSPROD.

Can you help to add the CODESMP in the Synthetic key fields list between the system table and the TRANSFERT_AVEC_REDRESS_M table.

Or help me to remove the loop.

Kind regards,

Abdallah

Re: Synthetic key fields

tulabandula — Fri, 27 Apr 2018 14:33:33 GMT

Hi Abdallah,

Please find the attached updated sheet .

you can join all the tables by avoiding multiple aliases as a key fields among the tables.

Re: Synthetic key fields

Mark_Little — Fri, 27 Apr 2018 14:41:30 GMT

Hi,

You have a couple of options, either don't load the duplicate fields, just load in your main table or Alias the duplicates.

Field as NewField.

Past that depending on your business case you may want to have a look at building a link table.

Mark

Re: Synthetic key fields

absekrafi — Fri, 27 Apr 2018 15:17:20 GMT

Thank you Mark,

I didn't load the duplicate fields (CODEMP and CODESMP): the circular referce is removed, but an other question arises:

The 2 fields disappeared completely from the Table TRANSFERT_AVEC_REDRESS_M and in my final application I will use these two codes in charts and pivot tables so I still need them.

In regards to the Link Table, (I am new to Qlikview), I don't know how to remove the system table (in the middle: it's created automaticly by qlikview).

Note: The synthetic Table is ($Syn 18 Table) in the picture I've sent.

Thank you again

Re: Synthetic key fields

absekrafi — Fri, 27 Apr 2018 15:34:51 GMT

Hi Sreeni,

I am not able to find the attached sheet. Where I can find it.

Thank you,

Abdallah

Re: Synthetic key fields

Anonymous — Fri, 27 Apr 2018 19:01:17 GMT

Hi,

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 application
We also have other methods to remove synthetic keys like “Creating a link table“, “Creating Explicit Joins ” and “Concatenating similar tables“.