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.
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 alias 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}
This statement creates a unique integer value for each distinct evaluated value in a field encountered during the script execution.
Autonumber fields [Using namespace] ]
The binary statement is used for loading the data from another QlikView document, including section access data.
Binary [path] filename
Provides a way of displaying the table comments (metadata) from databases or spreadsheets.
comment-table tablelist using mapname
comment-table tablename with comment
The CONNECT 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
The Declare statement is used to create field 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> ]
The Derive 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
The DIRECT QUERY statement allows you to access tables through an ODBC or OLE DB connection using the Direct Discovery function.
direct-query [path]
The Directory statement defines which directory to look in for data files in subsequent LOAD statements, until a new Directory statement is made.
Directory [path]
The Disconnect statement terminates the current ODBC/OLE DB/Custom connection. This statement is optional.
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 a drop field statement. The "distinct" property of a table is removed after a drop field statement.
drop-field fieldname [ , fieldname2 ...] [from tablename1 [ , tablename2 ...]]
drop fields fieldname [ , fieldname2 ...] [from tablename1 [ , tablename2 ...]]
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 a drop table statement.
drop-table tablename [, tablename2 ...]
drop tables[ tablename [, tablename2 ...]
The Execute statement is used to run other programs while Qlik Sense is loading data. For example, to make conversions that are necessary.
Execute commandline
The FlushLog statement forces Qlik Sense to write the content of the script buffer to the script log file.
The force statement forces Qlik Sense to interpret field names and 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 )
The LOAD 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 subsequent SELECT statement or by generating data automatically. It is also possible to load data from analytic connections.
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 ]
[extension pluginname.functionname(tabledescription)]
The let statement is a complement to the set statement, used for defining script variables. The let statement, in opposition to the set statement, evaluates the expression on the right side of the ' =' at script run time before it is assigned to the variable.
Let variablename=expression
One or more Qlik Sense internal data tables can be explicitly declared loosely coupled during script execution by using a Loosen Table statement. When a table is loosely coupled, all associations between field values in the table are removed. A similar effect could be achieved by loading each field of the loosely coupled table as independent, unconnected tables. Loosely coupled can be useful during testing to temporarily isolate different parts of the data structure. A loosely coupled table can be identified in the table viewer by the dotted lines. The use of one or more Loosen Table statements in the script will make Qlik Sense disregard any setting of tables as loosely coupled made before the script execution.
tablename [ , tablename2 ...]
Loosen Tables tablename [ , tablename2 ...]
The map ... 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 the Mapping statement.
Map *fieldlist Using mapname
The NullAsNull statement turns off the conversion of NULL values to string values previously set by a NullAsValue statement.
NullAsNull *fieldlist
The NullAsValue statement specifies for which fields that NULL should be converted to a value.
NullAsValue *fieldlist
The Qualify 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
The rem statement is used for inserting remarks, or comments, into the script, or to temporarily deactivate script statements without removing them.
Rem string
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 })
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 })
With the section 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)
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 ]
The set statement is used for defining script variables. These can be used for substituting strings, paths, drives, and so on.
Set variablename=string
The sleep statement pauses script execution for a specified time.
Sleep n
The SQL statement allows you to send an arbitrary SQL command through an ODBC or OLE DB connection.
SQL sql_command
The sqlcolumns statement returns a set of fields describing the columns of an ODBC or OLE DB data source, to which a connect has been made.
The sqltables statement returns a set of fields describing the tables of an ODBC or OLE DB data source, to which a connect has been made.
The sqltypes statement returns a set of fields describing the types of an ODBC or OLE DB data source, to which a connect has been made.
The string used for representing the set of all the values of a field in the database can be set through the star statement. It affects the subsequent LOAD and SELECT statements.
Star is [ string ]
The Store statement creates a QVD, or text file.
Store [ *fieldlist from] table into filename [ format-spec ];
This script statement provides a way to assign tags to one or more fields or tables. If an attempt to tag a field or table 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[field|fields] fieldlist with tagname
Tag [field|fields] fieldlist using mapname
Tag table tablelist with tagname
The trace 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 the trace statement, you can customize the message.
Trace string
The Unmap statement disables field value mapping specified by a previous Map … Using statement for subsequently loaded fields.
Unmap *fieldlist
The Unqualify statement is used for switching off the qualification of field names that has been previously switched on by the Qualify statement.
Unqualify *fieldlist
This script statement provides a way to remove tags from fields or tables. If an attempt to untag a field or table not present in the app is made, the untagging will be ignored.
Untag[field|fields] fieldlist with tagname
Tag [field|fields] fieldlist using mapname
Tag table tablelist with tagname