기본 콘텐츠로 건너뛰기

Understanding script syntax and data structures

이 페이지에서

관련 학습 자료

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.

Qlik Sense에서 데이터를 효율적으로 처리할 수 있는 데이터 모델을 만들려고 합니다. 일반적으로 이런 경우, 순환 참조 없이 적절히 정규화된 스타 스키마 또는 눈송이형 스키마, 즉 각 엔터티가 별도의 테이블에 보관되는 모델을 사용해야 합니다. 다시 말해, 일반적인 데이터 모델은 다음과 같습니다.

  • 측정값(단위 숫자, 판매 금액, 예산 등)을 계산하는 데 사용되는 숫자 및 차원에 대한 키가 포함된 중앙 팩트 테이블.
  • 해당하는 모든 특성(제품, 고객, 범주, 캘린더, 공급자 등)과 함께 차원이 포함된 주변 테이블.
참고: 많은 경우, 로드 스크립트에서 풍부한 데이터 모델을 구축하거나 차트 표현식에서 집계를 수행하여 집계 등의 작업을 처리할 수 있습니다. 대개는 로드 스크립트에서 데이터 변환을 수행하는 경우 더 나은 성능을 얻을 수 있습니다.
팁: 종이에 데이터 모델을 그려보는 것도 좋습니다. 그렇게 하면 추출할 데이터와 수행할 변환에 대한 구조를 파악하는 데 도움이 됩니다.

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