Understanding script syntax and data structures

Extract, transform, and load

In general, the way you load data into the app or script can be explained by the extract, transform and load process:

• Extract

  The first step is to extract data from the data source system. In a script, you use SELECT or LOAD statements to define this. The differences between these statements are:

  • SELECT is used to select data from an ODBC data source or OLE DB provider. The SELECT SQL statement is evaluated by the data provider, not by Qlik Sense.
  • LOAD is used to load data from a file, from data defined in the script, from a previously loaded table, from a web page, from the result of a subsequent SELECT statement or by generating data automatically.

• Transform

  The transformation stage involves manipulating the data using script functions and rules to derive the desired data model structure. Typical operations are:

  • Calculating new values
  • Translating coded values
  • Renaming fields
  • Joining tables
  • Aggregating values
  • Pivoting
  • Data validation

• Load or export

  In the final step, you run the script to load the data model you have defined into the app or load and export the data from your script.

Your goal should be to create a data model that enables efficient handling of the data in Qlik Sense. Usually this means that you should aim for a reasonably normalized star schema or snowflake schema without any circular references, that is, a model where each entity is kept in a separate table. In other words a typical data model would look like this:

• a central fact table containing keys to the dimensions and the numbers used to calculate measures (such as number of units, sales amounts, and budget amounts).
• surrounding tables containing the dimensions with all their attributes (such as products, customers, categories, calendar, and suppliers).

Data loading statements

Data is loaded by LOAD or SELECT statements. Each of these statements generates an internal table. A table can always be seen as a list of data, each record (row) then being a new instance of the object type and each field (column) being a specific attribute or property of the object.

The differences between these statements are:

• SELECT is used to select data from an ODBC data source or OLE DB provider. The SELECT SQL statement is evaluated by the data provider, not by Qlik Sense.
• LOAD is used to load data from a file, from data defined in the script, from a previously loaded table, from a web page, from the result of a subsequent SELECT statement or by generating data automatically.

Rules

The following rules apply when loading data into apps and scripts:

• Qlik Sense does not make any difference between tables generated by a LOAD or a SELECT statement. This means that if several tables are loaded, it does not matter whether the tables are loaded by LOAD or SELECT statements or by a mix of the two.
• The order of the fields in the statement or in the original table in the database is arbitrary to the Qlik Sense logic.
• Field names are used in the further process to identify fields and making associations. These are case sensitive, which often makes it necessary to rename fields in the script.

Execution of the script

For a typical LOAD or SELECT statement the order of events is roughly as follows:

1. Evaluation of expressions
2. Renaming of fields by as
3. Renaming of fields by alias
4. Qualification of field names
5. Mapping of data if field name matches
6. Storing data in an internal table