In principle, each Load or Select statement creates one table in the data model. There are, however, some exceptions to this rule:
- If a loaded table contains exactly the same set of fields as an existing data table, the new table will not be created as a separate table. Instead, it will be appended to the existing table.
- If the
LoadorSelectstatement is preceded by theConcatenatekeyword orJoin, the loaded table will be merged with an existing data table. - If the
LoadorSelectstatement is preceded by theGenerickeyword, the loaded table will be transformed into several data tables. - If the
LoadorSelectstatement is preceded by theMappingorSemantickeyword, the loaded table will not be used as a normal data table. - An existing table can be deleted using the
Dropcommand.
Several Load statements will thus normally create a data model with several tables.
In the end of the script run, the existing tables will be evaluated. If the same field name is found in more than one table, this field will be considered to be a key that links the two tables. This way, a data model is created. The logic in the script evaluation is hence identical in Qlik Sense and QlikView.
The data model defines how Qlik Sense's internal logical inference and calculations should be made.
A user selection implies a new logical situation. Other field values than the ones used before are possible; summations need to be made, so the charts and the KPIs get different values than what we got before. Everything needs to be recalculated, and the data model defines how this is done.
When the user makes a selection, Qlik Sense first evaluates which field values of other fields are possible and which records are possible. In this evaluation, the key fields are used to propagate the selection to other tables. The second step is to calculate all formulas based on the possible records. The data model thus defines how all calculations should be made.
When you create your data model, you should look out for two potential problems: synthetic keys and circular references. Synthetic keys are simply Qlik Sense's way of managing composite keys. Usually, you do not want synthetic keys—you want one single key in each table link. However, if you know that you have multiple keys linking two tables, and that is the way you want your data model, then there is no problem in having synthetic keys.
This is not the case for circular references. If you have a circular reference, you should rethink your data model with the goal of removing the loop. Qlik Sense will, at the end of the script run, warn you about these potential problems, as shown in the following screenshot:
