Vai al contenuto principale

Understanding script syntax and data structures

IN QUESTA PAGINA

APPRENDIMENTO CORRELATO

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.

L'obiettivo dovrebbe essere creare un modello dati che consenta una gestione efficiente dei dati in Qlik Sense. Di solito questo significa che si deve mirare a uno schema a fiocco di neve o a uno schema a stella ragionevolmente normalizzato senza alcun riferimento circolare, ossia, a un modello in cui ciascuna entità viene mantenuta in una tabella separata. In altre parole, un tipico modello dati è simile al seguente:

  • Una tabella dei fatti centrale contenente le chiavi per le dimensioni e i numeri utilizzati per calcolare le misure (ad esempio numero di unità, importi delle vendite e importi di budget).
  • Tabelle circostanti contenenti le dimensioni con tutti i relativi attributi (ad esempio prodotti, clienti, categorie, calendario e fornitori).
Nota: In molti casi è possibile risolvere un'attività, ad esempio le aggregazioni, mediante la creazione di un modello dati più ricco nello script Load o mediante l'esecuzione delle aggregazioni nelle espressioni per grafici. Come regola generale, si avranno prestazioni migliori se si mantengono le trasformazioni dei dati nello script Load.
Suggerimento: Si consiglia di fare uno schizzo del modello dati su carta. Ciò sarà utile perché fornirà la struttura per i dati da estrarre e le trasformazioni da eseguire.

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 something, 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