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, SELECT or map...using statement in the script. It
is only relevant during partial reloads.
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.
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.
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.
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.
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. .
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.
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.
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.
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.
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.
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.
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.
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.
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 )
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!
