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}
This statement creates a unique integer value for each distinct evaluated value in a field encountered during the script execution.
Autonumber *fieldlist
[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 QlikView 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.
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 QlikView 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 QlikView is loading data. For example, to make conversions that are necessary.
Execute commandline
The force statement forces QlikView 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
One or more QlikView internal data tables can be explicitly declared loosely coupled during script execution by using a Loosen Table statement. The use of one or more Loosen Table statements in the script will make QlikView disregard any setting of tables as loosely coupled made before the script execution.
loosen-table 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 QlikView 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 QlikView 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 ]
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 document 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 document 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