Script regular statements

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}

The binary statement is used for loading the data from another Qlik Sense app or QlikView 11.2 or earlier document, including section access data.

Binary file

file ::= [ path ] filename

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 *fieldlist using mapname

comment fieldname with comment

Provides a way of displaying the table comments (metadata) from databases or spreadsheets.

comment tables tablelist using mapname

comment tables 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 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> ]

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.

DirectQuery [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.

Disconnect

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.

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 ...]]

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.

The forms drop table and drop tables are both accepted.

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.

FlushLog

The force 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 )

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.

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 ]

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 ' =' before it is assigned to the variable.

Let variablename=expression

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.

SQLColumns

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.

SQLTables

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.

SQLTypes

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 ]

This script function creates a QVD or a CSV file.

Store [ *fieldlist from] table into filename [ format-spec ];

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

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

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

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!

Leave your feedback here