Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Vyas,
as per my knowledge there is no rule like for this situation we need to use and we can't use the other one.
so
Judgement will come spontaneous as per our convenience but all are the correct paths/ways only. Bansal is given a good explanation regarding this please check it once.
Synthetic table\keys in QlikView – and how to avoid it | Tech Updates
Synthetic table\keys in QlikView – and how to avoid it
I know this is a very basic thing in QlikView, and people who do work on Qlikview must have already experienced synthetic keys. But, since this is my first article on QV & this is something I came across with initially when I started with QV and frankly speaking it took me significant time to understand it completely and therefore thought of putting it here again in very simple words.
By definition- ‘Synthetic keys occur when two or more tables have two or more fields in common.’
What does this mean – If we have two or more tables with more than one common field (as by name – remember QV is case sensitive) then association between them is automatically formed by means of a composite key containing all those common fields. And this in QV is handled by creating an additional table called as synthetic table having all those common fields & distinct combination/values along with a primary key called as Synthetic Key (shown as $Syn) and the data tables are associated with that synthetic key in there.
So, in the above example, Name & RowStatus are the common fields and hence synthetic table & key is formed on the same i.e. a unique combination will be identified by these two fields. Now, let’s look at the data table (table box object) with all of the four fields.
This sounds quite actually right since this maps two tables based upon the common fields i.e. the composite key with no common record and serving the purpose well. But, this is not the case. Synthetic keys are not considered as that good and data model is considered to be of a bad design in this case.
There are various beliefs around whether generation of synthetic keys is always a bad choice or we should focus more on correcting our data model. So, at this point, we know why this synthetic table/key is formed by QV automatically but the question “Is this actually an error & always bad to have?” is still open.
Well, synthetic keys are ok to have in case of smaller datasets but if dataset is significantly large and/or there are many such common fields participating into synthetic keys then this will definitely give some performance implications and may also result memory issues. Hence, wherever possible we should avoid these.
However, having said that It’s important to look at the particular scenario/requirement and decide if we really want to get rid of the synthetic key. Here in the above example, these common appearing fields are not exactly the same & serving the same purpose. And hence, they should be avoided to form a synthetic key.
There are various ways to avoid synthetic table/key as follows, although it depends upon the requirement which one among these to make use of.
1. By removing those common fields: if we these common fields causing the synthetic keys are not required, we should remove/comments them out.
2. Renaming / Aliasing the fields: If fields common between the two tables are actually the different fields, serving the different purpose, these should be renamed (by providing some different aliases using ‘As’ clause) as per their table.
E.g. In the above scenario –
Product : ProductID, Name As ProductName, RowStatus
Client: ClientID, Name As ClientName, RowStatus
(RowStatus can also be renamed further to ProductRowStatus & ClientRowStatus respectively)
These two tables are automatically connected over here, this is the associative nature of QlikView.
3. Using Qualify: Another approach similar to renaming the fields is specifying the Qualify keyword. This enforces all the selected fields to have the fully qualified name in the format of TableName.FieldName
This is achieved by specifying Qualify *; or Qualify column-names (if some of the selected fields to be specified). And we need to exclude a few of them from having fully qualified name for certain reasons then use ‘UnQualify’ keyword.
4. Joining of tables: We can also make use of JOIN and explicitly join these two tables.
<Difference between aliasing and joining of tables is that in case of Join, single table is created.>
5. Concatenating two tables: Two tables are concatenated in QlikView when number of fields in both the tables and their names are same. Order of the fields may be different though.
6. Using complex/composite key: As we have seen in above examples, synthetic table contains a synthetic key that is a composite of all the combinations of the multiple key fields connecting the tables. This synthetic field, denoted again by the $Syn symbol, is also placed in the original data fields connected by multiple fields. This new synthetic field is called a synthetic key. So, if we already join those two or more than two fields causing a synthetic table by creating a complex key like ‘ProductId_Name’ (productID & ‘_’ & Name) and thus having only one combined field/key instead of two separate fields.
7. Creating a Link table: A key/link table is frequently required in QlikView to resolve Synthetic Key or Circular Join issues. Creation & usage of a Link table is a broader topic to discuss, hence we will talk about this in detail in next article.
