Understanding script syntax and data structures

Extract, transform, and load

In general, the way you load data into the app 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

  In the final step, you run the script to load the data model you have defined into the app.

Sie sollten ein Datenmodell erstellen, mit dem Sie die Daten in Qlik Sense effizient nutzen können. Im Regelfall sollten Sie sich für ein relativ normales Schema in Form von Stern oder Schneeflocke ohne Zirkelbezüge entscheiden. Hierbei handelt es sich um ein Modell, in dem jedes Element in einer getrennten Tabelle gespeichert wird. Ein häufig verwendetes Datenmodell sieht wie folgt aus:

• eine zentrale Tabelle mit Fakten, die Schlüssel zu den Dimensionen und Zahlen zur Berechnung der Kennzahlen enthält (z. B. Anzahl an Abschnitten, Umsatzzahlen und Budgetzahlen).
• umliegende Tabellen mit den Dimensionen mit allen Attributen (wie Produkte, Kunden, Kategorien, Kalender und Lieferanten).

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 Qlik Sense:

• 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