Regular statements are typically used for manipulating data in one way or another. These statements may be written over any number of lines in the script and must always be terminated by a semicolon, ";".
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.
Script regular statements overview
Each function is described further after the overview. You can also click the function name in the syntax to immediately access the details for that specific function.
Alias
Thealias statement is used for setting an alias according to which a field will be renamed whenever it occurs in the script that follows.
Alias fieldname as aliasname {,fieldname as aliasname}
Binary
Thebinary statement is used for loading the data from another Qlik Sense app or QlikView 11.2 or earlier document, including section access data. Other elements of the app are not included, for example, sheets, stories, visualizations, master items or variables.
Binary file
file ::= [ path ] filename comment
Provides a way of displaying the field comments (metadata) from databases and spreadsheets. Field names
not present in the app will be ignored. If multiple occurrences of a field name are found, the last value is used.
Comment field *fieldlist using mapname Comment field fieldname with comment comment table
Provides a way of displaying the table comments (metadata) from databases or spreadsheets.
Comment table tablelist using mapname Comment table tablename with comment Connect
TheCONNECT statement is used to define Qlik Sense access to a general database through the OLE DB/ODBC interface. For ODBC, the data source first needs to be specified using the ODBC administrator.
ODBC Connect TO connect-string [ ( access_info ) ] OLEDB CONNECT TO connect-string [ ( access_info ) ] CUSTOM CONNECT TO connect-string [ ( access_info ) ] LIB CONNECT TO connection
Declare
TheDeclare statement is used to create field and group definitions, where you can define relations between fields or functions. A set of field definitions can be used to automatically generate derived fields, which can be used as dimensions. For example, you can create a calendar definition, and use that to generate related dimensions, such as year, month, week and day, from a date field.
definition_name:
Declare [Field[s]] Definition [Tagged tag_list ] [Parameters parameter_list ]
Fields field_list [Groups group_list ]
<definition name>:
Declare [Field][s] Definition Using <existing_definition>
[With <parameter_assignment> ] Derive
TheDerive statement is used to generate derived fields based on a field definition created with a Declare statement. You can either specify which data fields to derive fields for, or derive them explicitly or implicitly based on field tags.
Derive [Field[s]] From [Field[s]] field_list Using definition
Derive [Field[s]] From Explicit [Tag[s]] (tag_list) Using definition Derive [Field[s]] From Implicit [Tag[s]] Using definition
Direct Query
TheDIRECT QUERY statement allows you to access tables through an ODBC or OLE DB connection using
the Direct Discovery function.
Direct Query [path]
Directory
TheDirectory statement defines which directory to look in for data files in subsequent LOAD statements, until a newDirectory statement is made.
Directory [path]
Disconnect
TheDisconnect statement terminates the current ODBC/OLE DB/Custom connection. This statement is optional.
Disconnect drop field
One or several Qlik Sense fields can be dropped from the data model, and thus from memory, at any time during script execution, by means of adrop field statement.
Both drop field and drop fields are allowed forms with no difference in effect. If no table is specified, the field will be dropped from all tables where it occurs.
Drop field fieldname [ , fieldname2 ...] [from tablename1 [ , tablename2 ...]]
drop fields fieldname [ , fieldname2 ...] [from tablename1 [ , tablename2 ...]]
drop table
One or several Qlik Sense internal tables can be dropped from the data model, and thus from memory, at any time during script execution, by means of adrop table statement.
The forms drop table and drop tables are both accepted.
Drop table tablename [, tablename2 ...]
drop tables[ tablename [, tablename2 ...]
Execute
TheExecute statement is used to run other programs while Qlik Sense is loading data. For example, to make conversions that are necessary.
Execute commandline FlushLog
TheFlushLog statement forces Qlik Sense to write the content of the script buffer to the script log file.
FlushLog
Force
Theforce statement forces Qlik Sense to interpret field values of subsequent LOAD and SELECT statements as written with only upper case letters, with only lower case letters, as always capitalized or as they appear (mixed). This statement makes it possible to associate field values from tables made according to different conventions.
Force ( capitalization | case upper | case lower | case mixed ) LOAD
TheLOAD statement loads fields from a file, from data defined in the script, from a previously loaded table, from a web page, from the result of a subsequentSELECT statement or by generating data automatically.
Load [ distinct ] *fieldlist [( from file [ format-spec ] |
from_field fieldassource [format-spec]
inline data [ format-spec ] | resident table-label |
autogenerate size )]
[ where criterion | while criterion ] [ group_by groupbyfieldlist ]
[order_by orderbyfieldlist ] Let
Thelet statement is a complement to the set statement, used for defining script variables. The let
statement, in opposition to theset statement, evaluates the expression on the right side of the ' =' before it is assigned to the variable.
Let variablename=expression Map ... using
Themap ... using statement is used for mapping a certain field value or expression to the values of a specific mapping table. The mapping table is created through theMapping statement.
Map *fieldlist Using mapname NullAsNull
TheNullAsNull statement turns off the conversion of NULL values to string values previously set by a NullAsValue statement.
NullAsNull *fieldlist NullAsValue
TheNullAsValue statement specifies for which fields that NULL should be converted to a value.
NullAsValue *fieldlist Qualify
TheQualify statement is used for switching on the qualification of field names, i.e. field names will get the
table name as a prefix.
Qualify *fieldlist Rem
Therem statement is used for inserting remarks, or comments, into the script, or to temporarily deactivate script statements without removing them.
Rem string Rename Field
This script function renames one or more existing Qlik Sense field(s) after they have been loaded.
Rename field (using mapname | oldname to newname{ , oldname to newname })
Rename Fields (using mapname | oldname to newname{ , oldname to newname }) Rename Table
This script function renames one or more existing Qlik Sense internal table(s) after they have been loaded.
Rename table (using mapname | oldname to newname{ , oldname to newname }) Rename Tables (using mapname | oldname to newname{ , oldname to newname }) Section
With thesection statement, it is possible to define whether the subsequent LOAD and SELECT statements should be considered as data or as a definition of the access rights.
Section (access | application) Select
The selection of fields from an ODBC data source or OLE DB provider is made through standard SQL SELECT statements. However, whether the SELECT statements are accepted depends on the ODBC driver or OLE DB provider used.
Select [all | distinct | distinctrow | top n [percent] ] *fieldlist From tablelist
[Where criterion ]
[Group by fieldlist [having criterion ] ] [Order by fieldlist [asc | desc] ]
[ (Inner | Left | Right | Full)Join tablename on fieldref = fieldref ] Set
Theset statement is used for defining script variables. These can be used for substituting strings, paths, drives, and so on.
Set variablename=string
Sleep
Thesleep statement pauses script execution for a specified time.
Sleep n SQL
TheSQL statement allows you to send an arbitrary SQL command through an ODBC or OLE DB connection.
SQL sql_command SQLColumns
Thesqlcolumns statement returns a set of fields describing the columns of an ODBC or OLE DB data source, to which aconnect has been made.
SQLColumns SQLTables
Thesqltables statement returns a set of fields describing the tables of an ODBC or OLE DB data source, to which aconnect has been made.
SQLTables SQLTypes
Thesqltypes statement returns a set of fields describing the types of an ODBC or OLE DB data source, to which aconnect has been made.
SQLTypes Star
The string used for representing the set of all the values of a field in the database can be set through thestar statement. It affects the subsequentLOAD and SELECT statements.
Star is [ string ] Store
This script function creates a QVD or a CSV file.
Store [ *fieldlist from] table into filename [ format-spec ];
Tag
This script function provides a way of assigning tags to one or more fields. If an attempt to tag a field name not present in the app is made, the tagging will be ignored. If conflicting occurrences of a field or tag name are found, the last value is used.
Tag fields fieldlist using mapname Tag field fieldname with tagname Trace
Thetrace statement writes a string to the Script Execution Progress window and to the script log file,
when used. It is very useful for debugging purposes. Using $-expansions of variables that are calculated prior to thetrace statement, you can customize the message.
Trace string Unmap
TheUnmap statement disables field value mapping specified by a previous Map … Using statement for subsequently loaded fields.
Unmap *fieldlist Unqualify
TheUnqualify statement is used for switching off the qualification of field names that has been previously switched on by theQualify statement.
Unqualify *fieldlist Untag
Provides a way of removing tags from one or more fields. If an attempt to untag a Field name not present in the app is made, the untagging will be ignored. If conflicting occurrences of a field or tag name is found, the last value is used.
Untag fields fieldlist using mapname Untag field fieldname with tagname
Alias
Thealias statement is used for setting an alias according to which a field will be renamed whenever it occurs in the script that follows.
Syntax:
alias fieldname as aliasname {,fieldname as aliasname}
Arguments:
Argument Description
fieldname The name of the field in your source data aliasname An alias name you want to use instead
Examples and results:
Example Result
Alias ID_N as NameID;
Example Result Alias A as
Name, B as Number, C as Date;
The name changes defined through this statement are used on all subsequent
SELECT and LOAD statements. A new alias can be defined for a field name by a new alias statement at any subsequent position in the script.
Binary
Thebinary statement is used for loading the data from another Qlik Sense app or QlikView 11.2 or earlier document, including section access data. Other elements of the app are not included, for example, sheets, stories, visualizations, master items or variables.
Only one binary statement is allowed in the script and it must be the first statement of the script, even before the SET statements usually located at the beginning of the script.
Syntax:
binary [path] filename Arguments:
Argument Description
filename The name of the file, including the file extension .qvw or .qvf.
path The path to the file which should be a reference to a folder data connection. This is required if the file is not located in the Qlik Sense working directory.
Example: 'lib://Table Files/'
In legacy scripting mode, the following path formats are also supported:
l absolute
Example: c:\data\
l relative to the app containing this script line.
Example: data\
Examples
Binary lib://MyData/customer.qvw; In this example, customer.qvw must be in located in the folder connected to the MyData data connection.
Binary customer.qvw; In this example, customer.qvw must be in located in the Qlik Sense working directory.
Binary c:\qv\customer.qvw; This example using an absolute file path will only work in legacy scripting mode.