The 'Load' Regular Statement

This article will cover the syntax of the ‘load’ regular script statement.

The load statement is used to load data into the Qlik Sense app. The syntax of the statement will vary depending on the source of the data.

Statement syntax

Copy code


LOAD [distinct] 
Fieldlist
From file [format-spec] | 
From_field fieldassource [format-spec] | 
Inline data [format-spec] |
Resident table-label |
Autogenerate size |
Extension pluginname.functionname ([script] tabledescription)
Where criterion | While criterion
Group by groupbyfieldlist
Having criterion
Order by orderbyfieldlist

Distinct	Using the ‘distinct’ keyword ensures that only unique records are loaded. If any duplicate rows exist, only the first record will be loaded.
Fieldlist	A list of fields to be loaded. Fields should be separated by a comma. A * character can be used to indicate that all fields available should be loaded.
From	If the data is to be loaded from a file or a data connection, then ‘from’ is used. The file path or data connection address is specified after ‘from’ followed by the format spec. The format spec specifies details regarding the file, e.g. if the file is a QVD file then the spec would be (qvd).
From_field	The ‘from_field’ keyword is used if the data is to be loaded from a previously loaded field. The ‘from_field’ keyword must be followed by the table name and field name from which the data is to be loaded.
Inline	If the data table is to be typed directly in the script, ‘inline’ is used. Following the keyword, the data must be typed in square brackets.
Resident	‘Resident’ is used when the data is to be loaded from a previously loaded table. The keyword must be followed by the table name from which the data is to be loaded.
Autogenerate	The ‘autogenerate’ keyword indicates that the data is to be generated by Qlik Sense. The size of the table required must be specified after the keyword. The size is the number of rows required.
Extension	The ‘extension’ keyword indicates that the data is to be loaded from an analytics connection. The keyword must be followed by the name of the connection and the function name required. You can specify the script needed for the function to run.
Where	‘Where’ is used to restrict which records are loaded into the app. Logical criteria should be specified after the ‘where’ keyword. A record will be loaded if it meets the condition.
While	‘While’ is used to specify whether a record should be read multiple times. Logical criteria should be specified after the ‘while’ keyword.
Group by	‘Group by’ is required for specifying which fields to aggregate over when an expression is used to create new calculated fields.
Having	‘Having’ is used to restrict which groups are loaded. This is only applicable where records have been aggregated into groups. Logical criteria should be specified after the ‘having’ keyword. A group will be loaded if it meets the condition.
Order by	‘Order by’ is used to specify how to order the records in the resident table before they are read by the load statement.

Example

We load a table containing a list of order IDs and the IDs of items they contained. We join a table containing details of each of the items. We then load a second table from the resident table to calculate the total cost of each order.

Copy code


ORDER_ITEMS:
LOAD
    orderId,
    itemId
FROM [lib://Public:DataFiles/order_items.xlsx]
(ooxml, embedded labels, table is Sheet1);
LEFT JOIN
LOAD
    id as itemId,
    cost,
    type,
    flavour
FROM [lib://Public:DataFiles/item_details.xlsx]
(ooxml, embedded labels, table is Sheet1);
ORDER_COST:
LOAD
	orderId,
    sum(cost) as totalCost
RESIDENT ORDER_ITEMS
GROUP BY
	orderId;