Synthetic Keys in Qlikview

Introduction to QlikView:

QlikView is one of the top business intelligence tools which provide an end-to-end platform service. The main operations of the QlikView tool included are data integration, user-driven business intelligence, and data analysis. The QlikView business tool helps users to convert the raw data into a useful one. This tool sometimes acts as a “Human brain” and mainly works on business associations. This software tool was first found in 1993 in Lund, Sweden, and is now based in King Of Prussia, Pennsylvania, United States.

One more important thing about QlikView is that even non-programmer also explores the visual data information view. With the help of clicks and taps, users can stream QlikView stream of various questions. QlikView software engine helps to calculate the new information, and data report. The word “Qlik” stands for software vendor data visualization, dashboard executives, and self-service business intelligence platform. This is one of the most data visualization platforms when compares to other products such as Tableau and Microsoft.

Want to get certified in QlikView. Learn from our experts and do excel in your career with HKR'S QlikView Online Training

What do you mean by synthetic key in QlikView?

The synthetic key in QlikView is usually created when two or more QlikView tables consist of more than one common field that also acts as a composite key connection between the tables. With the help of synthetic keys, QlikView handles the composite keys generation between any two tables. These keys are also popularly known as the Composite key combination.

When a user loads the data into the table, if you get any warning message related to synthetic keys, then it’s always a good idea to review the data structure in the QlikView table viewer. And also user needs to verify whether the received data model is correct or not. One more important point to be noted, multiple synthetic keys always represent a symptom of any incorrect QlikView data model and you may get an error in the script.

What are the purposes to remove Synthetic keys in QlikView tables?

The following are the major reasons to remove the synthetic keys in QlikView;

1. You should remember that Synthetic keys never harm your data model. It also supports easy handling of composite keys and also helps the user to verify the data which is used in the data model.

2. When you are dealing with more synthetic keys, the QlikView platform may take more and investments, this depends on the amount of data used, structures, and table fields.

3. By using synthetic key values, you cannot predict the limitation of the practical method which determines the trial and error methods.

4. Suppose if you are using more Synthetic key values and more data model structures, then its results in poor performance of the tool.

What are the advantages of using synthetic key values in QlikView?

The below are the few advantages of using Synthetic key values in QlikView:

1. Synthetic key values improve the performance of the data model, and also help to verify the structure.

2. Synthetic key values also help users to get the best result of the data model.

3. These key values are also used as a composite key value to get good clarity about the data model structure in QlikView.

4. Synthetic key values in QlikView can be used to detect the flaws in the data model structure.

5. We can call synthetic key values from other names, Pseudo keys, system-generated keys, factless keys, database sequence number, technical key, entity identifier, and arbitrary unique identifier.

How to create Synthetic key values in QlikView?

To create synthetic key values in the QlikView table, you should follow the below steps:

1. Input data:

This is the first step, initially, you need to consider the input data files -> they can also be used as an input data illustrator.

2. Loading of data:

Let’s begin with loading the two tables’ data into QlikView script or memory, > this can be of product details, sales details, and any two common fields in both the files. -> Now you can view the product view and subcategory.

Click her to get frequently asked QlikView interview question & answers

Once you are done with this step, data will be loaded into the table field -> then you need to save them in memory. You can also use shortcut keys like Ctrl + T to view the data model structure.

3. Data model:

This is the third table that provides the value of the synthetic key as any two table fields which consist of common fields. For example, Product ID and subcategory data fields as shown below;

How to remove the synthetic keys?

To remove any synthetic key field you should follow the below steps. Let’s discuss them in detail:

1. Rename the fields:

Just by renaming you can avoid the common field names in the data model. For example; in the below diagram you can see the two files with the same name namely city, so you can rename it as Dim_city that helps to avoid the generation of synthetic key values.

2. Remove the fields:

The fields which are commonly used in the QlikView table to create a synthetic key value. You can remove such fields by making a comment just before the filed name by inserting the comment sign “//” to the filed name in the script. In the below code, you can see there are two common fields available called the city, one of the fields will be removed by using // sign like //city.

3. Rename the fields by using Qualify:

You can also rename the data field by using one more keyword called “Qualify”. You need to rename the two common data fields to avoid the generation of synthetic key values. With the help of the Qualify keyword, the field name will be converted into “Table name”. In the below figure, you can rename the city field by using the qualify keyword and that will be converted into Dim. City.

4. By using auto number:

With the help of Autonumberhash256 and AutoNumber128 functions, you can resolve the synthetic functions. These functions will create the composite keys and also generates the 256 bits or 128 bits value for each key field combination.

How to fix synthetic key values?

Once you are done with removing the synthetic key values, then now it’s time to fix back these synthetic key values in the QlikView data model as artificial key values.

The below are the two steps used to fix synthetic key values:

1. Link or key table:

In this type, you are going to create synthetic key values as artificial key values to improve the data model performance and avoid any errors.

2. Concatenation:

In this type, you need to swap the matching and valid composite key values to get the appropriate Synthetic key values.

Note: important process to remove the synthetic key values through link table:

If you are done loading the data into the QlikView data model, then you should insert the synthetic key values in the data model. You can also remove these synthetic key values with the help of Link tables. For example, if this link table consists of common fields, and a combination of similar key values, then you can see it in the below diagram;

After removing the synthetic key values in the table data, you will get the result as shown below;

In the above diagram, you can see the link table that consists of common fields like Product Code, employee ID, key, and Branch.

Conclusion:

If you are a QlikView expert, then you should know about Synthetic key values while working with data table fields. The main purpose to create this synthetic key value is to speed up the memory and performance. More importantly, creating synthetic key value will also improve the performance of the data model. In this Synthetic key value in the QlikView blog, we have explained how to remove and create synthetic key value in the data table field. Learning this blog may help a few of you to master the concepts and apply them when needed.

Other Related Articles:

• QlikView Tutorial
• Incremental Load in QlikView
• Tableau vs QlikView