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 prefix is used to turn a cross table into a straight table, that is, a wide table with many columns is turned into a tall table, with the column headings being placed into a single attribute column.
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 unpacking and loading of a generic database can be done with a generic prefix.
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 in order 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 explicit Join prefix can be preceded by the prefix Outer in order to specify an outer join. In an outer join, the resulting table will contain all values from both raw tables where the linking field values are represented in either 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 )
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 )