So, let’s start tutorial on ” Synthetic Keys in QlikView”.<\/p>\n
![\"Create](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/10\/Resolving-Synthetic-Keys-01.jpg\")
<\/a>Create Synthetic Keys in QlikView – 4 Simple Ways to Resolve it<\/p><\/div>\n
QlikView Synthetic Keys<\/h3>\n
A synthetic key is a provisional key created in QlikView when there occurs a situation when there are more than two same fields between tables. In the star schema, we saw that the fact table had only one common field (unique key) between two tables. Whereas in the case of synthetic keys in QlikView, there are two or more common fields between two tables.<\/p>\n
To counter this confusion, a new table or synthetic key is created which shows the fields that are common. Whenever such a situation arises, the synthetic keys in QlikView creates automatically, and this change is also loaded into QlikView\u2019s memory.<\/p>\n
Do you know how to create QlikView Time Chart Wizard?\u00a0<\/a><\/strong><\/p>\n To create a synthetic key in Qlikview load the tables into QlikView\u2019s memory or script. The two data tables that we have used have two common fields i.e., Product ID and Sub-category.<\/p>\n LOAD [Product ID], LOAD [Product ID], As soon as the files are loaded and saved in memory. Upon pressing CTRL+T, the table structure display with a synthetic key created.<\/p>\n Create Synthetic Keys in QlikView<\/p><\/div>\n Follow this link to know how to apply QlikView Layout Themes?<\/a><\/strong><\/p>\n As you can see in the screenshot above, the two fields Product ID and Sub-Category are common in the tables titled \u2018Product Details\u2019 and \u2018Sales Details\u2019. A third table Syn1 Table (Synthetic key) create having the two common fields.<\/p>\n When you see the source table view where all the common fields between tables show to directly connect.<\/p>\n Synthetic Keys in QlikView – Source Table View<\/p><\/div>\n But the internal logic does not treat common fields between tables like this. Internally the common fields are shown in a separate table i.e. Syn Table. The syn table or synthetic key in QlikView, table contains the collection of all the composite keys between the tables. It is also called a symbol table for composite keys because of an identifier symbol like $Syn 1.<\/p>\n Synthetic Keys in QlikView – Internal Table View<\/p><\/div>\n QlikView Synthetic keys, perceive as bad, but they are not to blame. What synthetic keys truly reflect is the users\u2019 incapability in designing a good data model or data structure. So, instead of cringing on a synthetic key in QlikView, you must focus on building a better data structure so that no synthetic keys are formed.<\/p>\n So, the problem of QlikView synthetic keys resolves by removing the synthetic keys. Certain methods use to resolve synthetic keys in QlikView:<\/p>\n Such fields which are common between tables and are extra can potentially form a synthetic key in QlikView. You can remove the fields by making them a comment which is done by adding a \u2018\/\/\u2019 sign in front of that field or remove the field from the script.<\/p>\n In the sample script code given below, as you can see there are two tables \u2018Sales\u2019 and \u2018Branch Mapping\u2019. Two fields which are \u2018Branch\u2019 and \u2018Region\u2019 are common between the tables. This might form a synthetic key, so to avoid it, we have made the Region field a comment (\/\/ Region).<\/p>\n Sales:<\/p>\n LOAD Branch,<\/p>\n \/\/ Region<\/p>\n Empcode,<\/p>\n Product,<\/p>\n Sales<\/p>\n FROM<\/p>\n Sales.xlsx<\/p>\n (ooxml, embedded labels, table is Transaction);<\/p>\n Branch Mapping:<\/p>\n LOAD Branch,<\/p>\n Region,<\/p>\n Country,<\/p>\n Category<\/p>\n FROM<\/p>\n Sales.xlsx<\/p>\n (ooxml, embedded labels, table is Branch_Mapping);<\/p>\n Also, Read – QlikView Ports & Client Communication<\/strong><\/a><\/p>\n Another way to resolve synthetic keys in QlikView, is by renaming one of the two common keys. Like in the sample code below, we have renamed the field Region as Sales Region, so that the Regions field (common in both tables) are read as different fields and no synthetic key form.<\/p>\n Sales:<\/p>\n LOAD Branch,<\/p>\n Region as Sales Region,<\/p>\n Empcode,<\/p>\n Product,<\/p>\n SaIes<\/p>\n FROM<\/p>\n Sales.xlsx<\/p>\n (ooxml, embedded labels, table is Transaction);<\/p>\n Branch_Mapping:<\/p>\n LOAD Branch,<\/p>\n Region,<\/p>\n Country,<\/p>\n Category<\/p>\n FROM<\/p>\n Sales.xlsx<\/p>\n (ooxml, embedded labels, table is Branch_Mapping);<\/p>\n You can also rename a field using the keyword QUALIFY. This renames common fields in the format \u2018TableName.FieldName\u2019. This gives the fields unique identity and prevents from a synthetic key formation.<\/p>\n Qualify Region;<\/p>\n Sales:<\/p>\n LOAD Branch,<\/p>\n Region,<\/p>\n Empcode,<\/p>\n Product,<\/p>\n SaIes<\/p>\n FROM<\/p>\n Sales.xlsx<\/p>\n (ooxml, embedded labels, table is Transaction);<\/p>\n Branch_Mapping:<\/p>\n LOAD Branch,<\/p>\n Region,<\/p>\n Country,<\/p>\n Category<\/p>\n FROM<\/p>\n Sales.xlsx<\/p>\n (ooxml, embedded labels, table is Branch_Mapping);<\/p>\n For, instance, in the code shown above, we have qualified the common field Region. Now, in the resultant data structure table, the names of this field will change to \u2018Sales.Region\u2019 and \u2018Branch_Mapping.Region\u2019.<\/p>\n Let’s Explore QlikView Tables – Cross, Straight, Pivot and Mapping<\/a><\/strong><\/p>\n One more method of resolving synthetic keys in QlikView, is by using Autonumberhash128 or Autonumberhash256 functions. These functions create composite keys and generate a 128bit or 256bit values for each composite key field combination.<\/p>\n Sales:<\/p>\n LOAD Branch,<\/p>\n Region,<\/p>\n Empcode,<\/p>\n Product,<\/p>\n SaIes<\/p>\n Autonumber(Branch&Region) as Check<\/p>\n FROM<\/p>\n Sales.xlsx<\/p>\n (ooxml, embedded labels, table is Transaction);<\/p>\n Branch_Mapping:<\/p>\n LOAD \/\/Branch,<\/p>\n \/\/Region,<\/p>\n Country,<\/p>\n Category<\/p>\n Autonumber(Branch&Region) as Check<\/p>\n FROM<\/p>\n Sales.xlsx<\/p>\n (ooxml, embedded labels, table is Branch_Mapping);<\/p>\n This will create a bit value for the field combination \u2018Branch & Region\u2019 using the Autonumber() function.<\/p>\n So, this was all about QlikView Synthetic Keys. Hope you like our explanation.\u00a0<\/span><\/span><\/p>\n Thus, the creation of a synthetic key is a very convenient feature that clears all the confusion concerning common fields in tables. However, having them might impair the functioning of your application in terms of speed and memory issues. So, under best practices for performance optimization, it is suggested to not have any synthetic keys in your data model.<\/span><\/span><\/p>\n Furthermore, if you have any, feel free to share with us. We will get back to you.<\/p>\n Related Topic –\u00a0QlikView Management Service & QMC<\/a><\/strong><\/p>\nHow to Create QlikView Synthetic Keys?<\/h3>\n
\nCategory,
\n[Sub-Category],
\n[Product Name]
\nFROM
\n[C:\\Users\\HP\\Desktop\\Dataflair\\Product details.csv]
\n(txt, codepage is 1252, embedded labels, delimiter is ‘,’, msq);<\/p>\n
\n[Sub-Category],
\n[Sales ID],
\nSales,
\nQuantity,
\nDiscount,
\nProfit
\nFROM
\n[C:\\Users\\HP\\Desktop\\Dataflair\\Sales details.csv]
\n(txt, codepage is 1252, embedded labels, delimiter is ‘,’, msq);<\/p>\n![\"Create](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/10\/synthetickey.png\")
<\/a>![\"Create](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/10\/synthetickey1-3.png\")
<\/a>![\"Create](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/10\/synthetickey2-1.png\")
<\/a>How to Resolve Synthetic Keys in QlikView?<\/h3>\n
a. Removing Fields<\/h4>\n
b. Renaming Fields<\/h4>\n
c. Renaming Using QUALIFY<\/h4>\n
d. Using Autonumber\/Composite Keys<\/h4>\n
Conclusion<\/h3>\n