Advanced Scripting in Qlikview and Data Model Optimization
The Single most significant factor in whether a QlikView project is a success is a well designed data model and scripting.
Scripting syntax contains many different functions and statements, all of which help developers to refine data models. In this chapter, we will discuss circular references and how they are handled, using key (link) tables, mapping tables, concatenation of tables, interval matching, data islands, and metadata.
Circular references are a common issue that occurs when setting up a data model; these circular links occur if there are logical loops in a data structure because three or more tables have more than one path of association. These circular references have no clear start and finish, and result in unpredictable results.
QlikView provides an automatic solution when confronted with a circular reference— the software designates a loosely coupled table to one or more of the tables to break the logic loop. See the following diagram for an example of a loosely coupled table; note that the Customers table is connected to the other tables via a dotted line. This denotes that the Customers table is loosely coupled.
QlikView sets the loosely coupled table as the largest table in the loop (most often, a fact table). QlikView developers can also override this automatic setting of the loosely coupled table by declaring the table that should be considered as loosely coupled in the script, such as in the following code we tend to select a different table to be loosely coupled:
Select * from Master_Data;
Loosen table Orders;
The preceding Loosen statement instructs QlikView to loosen the Orders table in order to break the circular reference.
Users can also set the defaults for the handling of loosely coupled tables in the Loosely Coupled checkboxes on the Tables tab under the Document Properties menu for each table in the application.