Skip to content

Script Control Statements

The script consists of a number of statements. A statement can be either a regular script statement or a script control statement.

Control statements are typically used for controlling the flow of the script execution. Each clause of a control statement must be kept inside one script line and may be terminated by semicolon or end-of-line.

Prefixes are never applied to control statements, with the exceptions of the prefixes when and unless which may be used with a few specific control statements.

All script keywords can be typed with any combination of lower case and upper case characters.

Call

The call control statement calls a subroutine which must be defined by a previous sub statement.

call name ( [ paramlist ])

Argument Description
name The name of the subroutine.
paramlist A comma separated list of the actual parameters to be sent to the subroutine. Each item in the list may be a field name, a variable or an arbitrary expression.

The subroutine called by a call statement must be defined by a sub encountered earlier during script execution.

Parameters are copied into the subroutine and, if the parameter in the call statement is a variable and not an expression, copied back out again upon exiting the subroutine.

Since the call statement is a control statement and as such is ended with either a semicolon or end-of-line, it must not cross a line boundary.

This example lists all Qlik related files in a folder and its subfolders, and stores file information in a table. It is assumed that you have created a data connection named Apps to the folder .

The DoDir subroutine is called with the reference to the folder, 'lib://Apps', as parameter. Inside the subroutine, there is a recursive call, Call DoDir (Dir), that makes the function look for files recursively in subfolders.

sub DoDir (Root)
    For Each Ext in 'qvw', 'qvo', 'qvs', 'qvt', 'qvd', 'qvc', 'qvf'
      For Each File in filelist (Root&'\*.' &Ext)
       LOAD
        '$(File)' as Name,
         FileSize( '$(File)' ) as Size,
         FileTime( '$(File)' ) as FileTime
         autogenerate 1;
     Next File
    Next Ext
    For Each Dir in dirlist (Root&'\*' )
        Call DoDir (Dir)
    Next Dir
End Sub

Call DoDir ('lib://Apps')

Do..loop

The do..loop control statement is a script iteration construct which executes one or several statements until a logical condition is met.

Do [ ( while | until ) condition ] [statements]
[exit do [ ( when | unless )condition ] [statements]
loop[ ( while | until ) condition ]

Since the do..loop statement is a control statement and as such is ended with either a semicolon or end-of-line, each of its three possible clauses ( do , exit do and loop )must not cross a line boundary.

Argument Description
condition A logical expression evaluating to True or False.
statements Any group of one or more Qlik Sense script statements.
while / until The . Each condition is interpreted only the first time it is encountered but is evaluated for every time it encountered in the loop.
exit do If an exit do clause is encountered inside the loop, the execution of the script will be transferred to the first statement after the loop clause denoting the end of the loop. An exit do clause can be made conditional by the optional use of a when or unless suffix.
// LOAD files file1.csv..file9.csv

Set a=1;
Do while a<10
LOAD * from file$(a).csv;
Let a=a+1;
Loop

Exit script

This control statement stops script execution. It may be inserted anywhere in the script.

Exit Script [ (when | unless) condition ]

Since the exit script statement is a control statement and as such is ended with either a semicolon or end-of-line, it must not cross a line boundary.

Argument Description
condition A logical expression evaluating to True or False.
when / unless An exit script statement can be made conditional by the optional use of when or unless clause.

Examples

//Exit script

Exit Script;

//Exit script when a condition is fulfilled

Exit Script when a=1

For each..next

The for each..next control statement is a script iteration construct which executes one or several statements for each value in a comma separated list. The statements inside the loop enclosed by for and next will be executed for each value of the list.

Special syntax makes it possible to generate lists with file and directory names in the current directory.

for each var in list [statements] [ [ ( when| unless ) condition ][statements]next [var]

Argument Description
var A script variable name which will acquire a new value from list for each loop execution. If var is specified after next it must be the same variable name as the one found after the corresponding for each .

The value of the var variable may be changed by statements inside the loop, but this is not good programming practice.

If an exit for clause is encountered inside the loop, the execution of the script will be transferred to the first statement after the next clause denoting the end of the loop. An exit for clause can be made conditional by the optional use of a when or unless suffix.

Since the for each..next statement is a control statement and as such is ended with either a semicolon or end-of-line, each of its three possible clauses (for each,exit for and next ) must not cross a line boundary.

list := item { , item } item := constant | (expression) | filelist mask | dirlist mask | fieldvaluelist mask

Argument Description
constant Any number or string. Note that a string written directly in the script must be enclosed by single quotes. A string without single quotes will be interpreted as a variable, and the value of the variable will be used. Numbers do not need to be enclosed by single quotes.
expression An arbitrary expression.
mask

A filename or folder name mask which may include any valid filename characters as well as the standard wildcard characters, * and ? .

You can use absolute file paths or lib:// paths.

condition A logical expression evaluating to True or False.
statements Any group of one or more Qlik Sense script statements.
filelist mask

This syntax produces a comma separated list of all files in the current directory matching the filename mask.

This argument supports only library connections in standard mode. See File system access restriction
dirlist mask

This syntax produces a comma separated list of all folders in the current folder matching the folder name mask.

This argument supports only library connections in standard mode. See File system access restriction
fieldvaluelist mask This syntax iterates through the values of a field already loaded into Qlik Sense.

Example 1: Loading a list of files

// LOAD the files 1.csv, 3.csv, 7.csv and xyz.csv
for each a in 1,3,7,'xyz'
LOAD * from file$(a).csv;
next

Example 2: Creating a list of files on disk

This example loads a list of all Qlik Sense related files in a folder.

sub DoDir (Root)
 for each Ext in 'qvw', 'qva', 'qvo', 'qvs', 'qvc','qvf', 'qvd' for each File in filelist (Root&'\*.' &Ext)
 LOAD
  '$(File)' as Name,
   FileSize( '$(File)' ) as Size,
    FileTime( '$(File)' ) as FileTime
    autogenerate 1;
    next File
    next Ext for each Dir in dirlist (Root&'\*' )
    call DoDir (Dir)
    next Dir
    end sub
    call DoDir ('lib://MyData')

Example 3: Iterating through a the values of a field

This example iterates through the list of loaded values of FIELD and generates a new field, NEWFIELD. For each value of FIELD, two NEWFIELD records will be created.

load * inline [
FIELD
one
two
three
];

FOR Each a in FieldValueList('FIELD')
LOAD '$(a)' &'-'&RecNo() as NEWFIELD AutoGenerate 2;
NEXT a

The resulting table looks like this:

NEWFIELD
one-1
one-2
two-1
two-2
three-1
three-2

For..next

The for..next control statement is a script iteration construct with a counter. The statements inside the loop enclosed by for and next will be executed for each value of the counter variable between specified low and high limits.

For counter = expr1 to expr2 [ step expr3 ][statements][exit for [ ( when | unless ) condition ][statements]

Next [counter]

The expressions, expr2 and expr3 are only evaluated the first time the loop is entered. The value of the counter variable may be changed by statements inside the loop, but this is not good programming practice.

If an exit for clause is encountered inside the loop, the execution of the script will be transferred to the first statement after the next clause denoting the end of the loop. An exit for clause can be made conditional by the optional use of a when or unless suffix.

Since the for..next statement is a control statement and as such is ended with either a semicolon or end-of-line, each of its three possible clauses ( for..to..step , exit for and next ) must not cross a line boundary.

Argument Description
counter A variable name. If counter is specified after next it must be the same variable name as the one found after the corresponding for.
expr1 An expression which determines the first value of the counter variable for which the loop should be executed.
expr2 An expression which determines the last value of the variable for which the loop should be executed.
expr3 An expression which determines the value indicating the increment of the counter variable each time the loop has been executed.
condition a logical expression evaluating to True or False.
statements Any group of one or more Qlik Sense script statements.

Example 1: Loading a sequence of files

// LOAD files file1.csv..file9.csv

for a=1 to 9
 LOAD * from file$(a).csv;
next

Example 2: Loading a random number of files

In this example, we assume there are data files x1.csv, x3.csv, x5.csv, x7.csv and x9.csv. Loading is stopped at a random point using the if rand( )<0.5 then condition.

for counter=1 to 9 step 2
set filename=x$(counter).csv;
if rand( )<0.5 then
    exit for unless counter=1
end if
LOAD a,b from $(filename);
next

If..then..elseif..else..end if

The if..then control statement is a script selection construct forcing the script execution to follow different paths depending on one or several logical conditions.

If condition then [ statements ] {elseif condition then [ statements ] }[ else [ statements ] ] end if

Since the if..then statement is a control statement and as such is ended with either a semicolon or end-of-line, each of its four possible clauses (if..then, elseif..then, else and end if) must not cross a line boundary.

Argument Description
condition A logical expression which can be evaluated as True or False.
statements Any group of one or more Qlik Sense script statements.

Example 1:

if a=1 then
    LOAD * from abc.csv;
    SQL SELECT e, f, g from tab1;
end if

Example 2:

if a=1 then; drop table xyz; end if;

Example 3:

if x>0 then
    LOAD * from pos.csv;
elseif x<0 then
    LOAD * from neg.csv;
else
    LOAD * from zero.txt;
end if

Sub..end sub

The sub..end sub control statement defines a subroutine which can be called upon from a call statement.

Sub name [ (paramlist ] statements end sub

Arguments are copied into the subroutine and, if the corresponding actual parameter in the call statement is a variable name, copied back out again upon exiting the subroutine.

If a subroutine has more formal parameters than actual parameters passed by a call statement, the extra parameters will be initialized to NULL and can be used as local variables within the subroutine.

Since the sub statement is a control statement and as such is ended with either a semicolon or end-of-line, each of its two clauses ( sub and end sub) must not cross a line boundary.

Argument Description
name The name of the subroutine.
paramlist A comma separated list of variable names for the formal parameters of the subroutine. These can be used as any variable inside the subroutine.
statements Any group of one or more Qlik Sense script statements.

Example 1:

Sub INCR (I,J)
I = I + 1
Exit Sub when I < 10
J = J + 1
End Sub
Call INCR (X,Y)

Example 2:- parameter transfer

Sub ParTrans (A,B,C)
A=A+1
B=B+1
C=C+1
End Sub
A=1
X=1
C=1
Call ParTrans (A, (X+1)*2)

The result of the above will be that locally, inside the subroutine, A will be initialized to 1, B will be initialized to 4 and C will be initialized to NULL.

When exiting the subroutine, the global variable A will get 2 as value (copied back from subroutine). The second actual parameter “(X+1)*2” will not be copied back since it is not a variable. Finally, the global variable C will not be affected by the subroutine call.

Switch..case..default..end switch The switch

The switch control statement is a script selection construct forcing the script execution to follow different paths, depending on the value of an expression.

Switch expression {case valuelist [ statements ]}[ statements] end switch

Since the switch statement is a control statement and as such is ended with either a semicolon or end-of-line, each of its four possible clauses (switch, case, default and end switch) must not cross a line boundary.

Argument Description
expression An arbitrary expression.
valuelist A comma separated list of values with which the value of expression will be compared. Execution of the script will continue with the statements in the first group encountered with a value in valuelist equal to the value in expression. Each value in valuelist may be an arbitrary expression. If no match is found in any case clause, the statements under the default clause, if specified, will be executed.
statements Any group of one or more Qlik Sense script statements.

Example:

Switch I
Case 1
LOAD '$(I): CASE 1' as case autogenerate 1;
Case 2
LOAD '$(I): CASE 2' as case autogenerate 1;
Default
LOAD '$(I): DEFAULT' as case autogenerate 1;
End Switch