Prefixes may be applied to applicable regular statements but never to control statements. The when and unless prefixes can however be used as suffixes to a few specific control statement clauses.
All script keywords can be typed with any combination of lower case and upper case characters. Field and variable names used in the statements are however case sensitive.
Use the drop-down on each function to see a brief description and the syntax of each function. Click the function name in the syntax description for further details.
The Add prefix can be added to any LOAD or SELECT statement in the script to specify that it should add records to another table. It also specifies that this statement should be run in a partial reload. The Add prefix can also be used in a Map statement.
Add [only] [Concatenate[(tablename )]] (loadstatement | selectstatement)
Add [ Only ] mapstatement
QVD files can be created and maintained automatically via the buffer prefix. This prefix can be used on most LOAD and SELECT statements in script. It indicates that QVD files are used to cache/buffer the result of the statement.
Buffer[(option [ , option])] ( loadstatement | selectstatement )
option::= incremental | stale [after] amount [(days | hours)]
If two tables that are to be concatenated have different sets of fields, concatenation of two tables can still be forced with the Concatenate prefix.
concatenate[ (tablename ) ] ( loadstatement | selectstatement )
The crosstable load prefix is used to transpose “cross table” or “pivot table” structured data. Data structured this way is commonly encountered when working with spreadsheet sources. The output and aim of the crosstable load prefix is to transpose such structures into a regular column-oriented table equivalent, as this structure is generally better suited for analysis in Qlik Sense.
crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )
The First prefix to a LOAD or SELECT (SQL) statement is used for loading a set maximum number of records from a data source table.
First n( loadstatement | selectstatement )
The Generic load prefix allows for conversion of entity–attribute–value modeled data (EAV) into a traditional, normalized relational table structure. EAV modeling is alternatively referred to as "generic data modeling" or "open schema".
Generic ( loadstatement | selectstatement )
The hierarchy prefix is used to transform a parent-child hierarchy table to a table that is useful in a Qlik Sense data model. It can be put in front of a LOAD or a SELECT statement and will use the result of the loading statement as input for a table transformation.
Hierarchy (NodeID, ParentID, NodeName, [ParentName], [PathSource], [PathName], [PathDelimiter], [Depth])(loadstatement | selectstatement)
This prefix is used to transform a parent-child hierarchy table to a table that is useful in a Qlik Sense data model. It can be put in front of a LOAD or a SELECT statement and will use the result of the loading statement as input for a table transformation.
HierarchyBelongsTo (NodeID, ParentID, NodeName, AncestorID, AncestorName, [DepthDiff])(loadstatement | selectstatement)
The join and keep prefixes can be preceded by the prefix inner.
If used before join it specifies that an inner join should be used. The resulting table will thus only contain combinations of field values from the raw data tables where the linking field values are represented in both tables. If used before keep, it specifies that both raw data tables should be reduced to their common intersection before being stored in Qlik Sense.
.Inner ( Join | Keep) [ (tablename) ](loadstatement |selectstatement )
The IntervalMatch prefix is used to create a table matching discrete numeric values to one or more numeric intervals, and optionally matching the values of one or several additional keys.
IntervalMatch (matchfield)(loadstatement | selectstatement )
IntervalMatch (matchfield,keyfield1 [ , keyfield2, ... keyfield5 ] ) (loadstatement | selectstatement )
The join prefix joins the loaded table with an existing named table or the last previously created data table.
[Inner | Outer | Left | Right ] Join [ (tablename ) ]( loadstatement | selectstatement )
The keep prefix is similar to the join prefix. Just as the join prefix, it compares the loaded table with an existing named table or the last previously created data table, but instead of joining the loaded table with an existing table, it has the effect of reducing one or both of the two tables before they are stored in Qlik Sense, based on the intersection of table data. The comparison made is equivalent to a natural join made over all the common fields, i.e. the same way as in a corresponding join. However, the two tables are not joined and will be kept in Qlik Sense as two separately named tables.
(Inner | Left | Right) Keep [(tablename ) ]( loadstatement | selectstatement )
The Join and Keep prefixes can be preceded by the prefix left.
If used before join it specifies that a left join should be used. The resulting table will only contain combinations of field values from the raw data tables where the linking field values are represented in the first table. If used before keep, it specifies that the second raw data table should be reduced to its common intersection with the first table, before being stored in Qlik Sense.
Left ( Join | Keep) [ (tablename) ](loadstatement |selectstatement )
The mapping prefix is used to create a mapping table that can be used to, for example, replacing field values and field names during script execution.
Mapping ( loadstatement | selectstatement )
The Merge prefix can be added to any LOAD or SELECT statement in the script to specify that the loaded table should be merged into another table. It also specifies that this statement should be run in a partial reload.
Merge [only] [(SequenceNoField [, SequenceNoVar])] On ListOfKeys [Concatenate [(TableName)]] (loadstatement | selectstatement)
The NoConcatenate prefix forces two loaded tables with identical field sets to be treated as two separate internal tables, when they would otherwise be automatically concatenated.
NoConcatenate( loadstatement | selectstatement )
The explicit Join prefix can be preceded by the prefix Outer to specify an outer join. In an outer join, all combinations between the two tables are generated. The resulting table will thus contain combinations of field values from the raw data tables where the linking field values are represented in one or both tables. The Outer keyword is optional and is the default join type used when a join prefix is not specified.
Outer Join [ (tablename) ](loadstatement |selectstatement )
A full reload always starts by deleting all tables in the existing data model, and then runs the load script.
A Partial reload will not do this. Instead it keeps all tables in the data model and then executes only Load and Select statements preceded by an Add, Merge, or Replace prefix. Other data tables are not affected by the command. The only argument denotes that the statement should be executed only during partial reloads, and should be disregarded during full reloads. The following table summarizes statement execution for partial and full reloads.
The Replace prefix can be added to any LOAD or SELECT statement in the script to specify that the loaded table should replace another table. It also specifies that this statement should be run in a partial reload. The Replace prefix can also be used in a Map statement.
Replace [only] [Concatenate[(tablename) ]] (loadstatement | selectstatement)
Replace [only] mapstatement
The Join and Keep prefixes can be preceded by the prefix right.
If used before join it specifies that a right join should be used. The resulting table will only contain combinations of field values from the raw data tables where the linking field values are represented in the second table. If used before keep, it specifies that the first raw data table should be reduced to its common intersection with the second table, before being stored in Qlik Sense.
Right (Join | Keep) [(tablename)](loadstatement |selectstatement )
The sample prefix to a LOAD or SELECT statement is used for loading a random sample of records from the data source.
Sample p ( loadstatement | selectstatement )
Tables containing relations between records can be loaded through a semantic prefix. This can for example be self-references within a table, where one record points to another, such as parent, belongs to, or predecessor.
Semantic ( loadstatement | selectstatement)
The unless prefix and suffix is used for creating a conditional clause which determines whether a statement or exit clause should be evaluated or not. It may be seen as a compact alternative to the full if..end if statement.
(Unless condition statement | exitstatement Unless condition )
The when prefix and suffix is used for creating a conditional clause which determines whether a statement or exit clause should be executed or not. It may be seen as a compact alternative to the full if..end if statement.
( When condition statement | exitstatement when condition )