How do you use the Qualify and Unqualify statements to resolve synthetic keys and loops?

1 What are synthetic keys and loops?

Synthetic keys and loops are two common data modeling problems that occur when QlikView tries to associate multiple tables based on common fields. A synthetic key is a system-generated field that QlikView creates when there are more than one common field between two tables. A loop is a circular relationship between three or more tables that creates ambiguity and redundancy in the data model. Both synthetic keys and loops can cause slow performance, memory consumption, and incorrect calculations in your QlikView applications.

2 How to identify synthetic keys and loops?

You can identify synthetic keys and loops by using the Table Viewer in QlikView. The Table Viewer is a graphical representation of your data model that shows the tables, fields, and associations in your QlikView document. To open the Table Viewer, go to the menu bar and click on Tools > Table Viewer. A synthetic key will appear as a table with a dollar sign ($) prefix and a composite field name that contains the common fields between the tables. A loop will appear as a circular chain of tables that have more than one association between them.

4 Example of Qualify and Unqualify statements

Let's look at an example of how to use the Qualify and Unqualify statements to resolve a synthetic key and a loop in a data model. Suppose you have three tables: Customers, Orders, and Products, that have the following fields:

Customers: CustomerID, Name, Country, Region

Orders: OrderID, CustomerID, ProductID, Quantity, Price

Products: ProductID, Name, Category, Cost

If you load these tables without any modification, QlikView will create a synthetic key based on the common field Name between Customers and Products, and a loop based on the multiple associations between Customers, Orders, and Products. To avoid these issues, you can use the Qualify and Unqualify statements as follows:

Qualify *; // qualify all fields in all tables

Unqualify CustomerID, ProductID; // unqualify the key fields

Customers:

Load CustomerID, Name, Country, Region

From Customers.csv;

Orders:

Load OrderID, CustomerID, ProductID, Quantity, Price

From Orders.csv;

Products:

Load ProductID, Name, Category, Cost

From Products.csv;

By using the Qualify and Unqualify statements, you will have a clean and simple data model that has only two associations: CustomerID between Customers and Orders, and ProductID between Orders and Products. No synthetic keys or loops will be created.

5 Benefits of Qualify and Unqualify statements

Using the Qualify and Unqualify statements can help you improve your data model quality and performance in QlikView. By avoiding synthetic keys and loops, you can reduce the memory usage and calculation time of your QlikView applications. You can also simplify the field names and expressions in your charts and tables, making them easier to understand and maintain. Moreover, you can ensure the accuracy and consistency of your data analysis, as there will be no ambiguity or redundancy in your data model.

6 Best practices for Qualify and Unqualify statements

When designing a data model in QlikView, it is important to utilize Qualify and Unqualify statements to resolve synthetic keys and loops. Additionally, you should use meaningful and unique field names for key fields, and employ aliases or rename functions to change field names in scripts. Further, star schemas or snowflake schemas should be used to structure the data model, and joins or concatenations should be used to reduce the number of tables. Lastly, unnecessary or redundant fields should be avoided as they can create synthetic keys.

7 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?