When

The when prefix and suffix is used for creating a conditional clause which determines whether a statement or exit clause should be executed or not. It may be seen as a compact alternative to the full if..end if statement.

Syntax:

(when condition statement | exitstatement when condition )

Return data type: Boolean

In Qlik Sense, the Boolean true value is represented by -1, and the false value is represented by 0.

The statement or the exitstatement will only be executed if condition is evaluated to TRUE.

The When prefix may be used on statements which already have one or several other statements, including additional When or Unless prefixes.

When to use it

The When statement returns a Boolean result. Typically, this type of function will be used as a condition when the user would like to load or exclude parts of a script.

Arguments
Argument	Description
condition	A logical expression evaluating to TRUE or FALSE
statement	Any Qlik Sense script statement except control statements.
exitstatement	An exit for, exit do or exit sub clause or an exit script statement.

Regional settings

Unless otherwise specified, the examples in this topic use the following date format: MM/DD/YYYY. The date format is specified in the SET DateFormat statement in your data load script. The default date formatting may be different in your system, due to your regional settings and other factors. You can change the formats in the examples below to suit your requirements. Or you can change the formats in your load script to match these examples.

Default regional settings in apps are based on the regional system settings of the computer or server where Qlik Sense is installed. If the Qlik Sense server you are accessing is set to Sweden, the Data load editor will use Swedish regional settings for dates, time, and currency. These regional format settings are not related to the language displayed in the Qlik Sense user interface. Qlik Sense will be displayed in the same language as the browser you are using.

Function examples
Example	Result
exit script when A=1;	When the statement A=1 is evaluated to be TRUE, the script will stop.
when A=1 LOAD * from myfile.csv;	When the statement A=1 is evaluated to be TRUE, the myfile.csv will be loaded.
when A=1 unless B=2 drop table Tab1;	When the statement A=1 is evaluated to be TRUE, and if B=2 is evaluated to be FALSE, than the Tab1 table will be dropped.

Example 1 – When prefix

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

A dataset with dates and amounts that is sent to a table named ‘Transactions’.
The Let statement which states that the variable A is created and has the value of 1.
The When condition which provides the condition that if A equals 1, then the script will continue to load.

Load script

LET A = 1; 

WHEN A = 1 
 
Transactions: 
LOAD  
*  
Inline [ 
id, date, amount 
1, 08/30/2018, 23.56 
2, 09/07/2018, 556.31 
3, 09/16/2018, 5.75 
4, 09/22/2018, 125.00 
5, 09/22/2018, 484.21 
6, 09/22/2018, 59.18 
7, 09/23/2018, 177.42 
];

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

id
date
amount

Results table
id	date	amount
1	08/30/2018	23.56
2	09/07/2018	556.31
3	09/16/2018	5.75
4	09/22/2018	125.00
5	09/22/2018	484.21
6	09/22/2018	59.18
7	09/23/2018	177.42

Because the variable A is assigned the value of 1 at the start of the script, the condition following the When prefix is evaluated and returns a result of TRUE. Because it returns a TRUE result, the script continues to run the load statement. All the records from the results table can be seen.

If this variable value was set to any value not equal to 1, no data would be loaded into the data model.

Example 2 – When suffix

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

Three datasets with dates and amounts that are sent to a table named ‘Transactions’.
- The first dataset contains transactions 1-7.
- The second dataset contains transactions 8-14.
- The third dataset contains transactions 15-21.
A When condition which determines whether the ‘Transactions’ table contains more than ten rows. If any of the When statements are evaluated to be TRUE, the load script will stop. This condition is placed at the end of each of the three datasets.

Load script

Transactions: 
LOAD  
*  
Inline [ 
id, date, amount 
1, 08/30/2018, 23.56 
2, 09/07/2018, 556.31 
3, 09/16/2018, 5.75 
4, 09/22/2018, 125.00 
5, 09/22/2018, 484.21 
6, 09/22/2018, 59.18 
7, 09/23/2018, 177.42 
]; 
 
exit script when NoOfRows('Transactions') > 10 ; 

Concatenate
LOAD 
*  
Inline [ 
id, date, amount 
8, 10/01/2018, 164.27 
9, 10/03/2018, 384.00 
10, 10/06/2018, 25.82 
11, 10/09/2018, 312.00 
12, 10/15/2018, 4.56 
13, 10/16/2018, 90.24 
14, 10/18/2018, 19.32 
]; 

exit script when NoOfRows('Transactions') > 10 ; 
 
Concatenate 
LOAD 
*  
Inline [ 
id, date, amount 
15, 10/01/2018, 164.27 
16, 10/03/2018, 384.00 
17, 10/06/2018, 25.82 
18, 10/09/2018, 312.00 
19, 10/15/2018, 4.56 
20, 10/16/2018, 90.24 
21, 10/18/2018, 19.32 
]; 
 
exit script when NoOfRows('Transactions') > 10 ;

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

id
date
amount

Results table
id	date	amount
1	08/30/2018	23.56
2	09/07/2018	556.31
3	09/16/2018	5.75
4	09/22/2018	125.00
5	09/22/2018	484.21
6	09/22/2018	59.18
7	09/23/2018	177.42
8	10/01/2018	164.27
9	10/03/2018	384.00
10	10/06/2018	25.82
11	10/09/2018	312.00
12	10/15/2018	4.56
13	10/16/2018	90.24
14	10/18/2018	19.32

There are seven transactions in each of the three datasets. The first dataset contains transaction 1-7 and is loaded into the application. The When condition following this load statement is evaluated as FALSE because there are less than ten rows in the ‘Transactions’ table. The load script continues to the next dataset.

The second dataset contains transaction 8-14 and is loaded into the application. The second When condition evaluates as TRUE because there are more than ten rows in the ‘Transactions’ table. Therefore, the script terminates.

Example 3 – Multiple When prefixes

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

A dataset containing a single transaction is created as a table called 'Transactions'.
A For loop which is triggered contains two nested When conditions which evaluate whether:
1. There are less than 100 records in the 'Transactions' table.
2. The number of records in the 'Transactions' table is not a multiple of 6.

Load script

Transactions: 
Load 
	0 as id 
Autogenerate 1; 

For i = 1 to 100 
	when NoOfRows('Transactions') < 100 when mod(NoOfRows('Transactions'),6) <> 0 
		Concatenate 
    Load 
    	if(isnull(Peek(id)),1,peek(id)+1) as id 
      Autogenerate 7;	 
next i

Results

Load the data and open a sheet. Create a new table and add this field as a dimension:

The results table only shows the first five transaction IDs but the load script creates 36 rows and then terminates once the When condition is fulfilled.

Results table
id
0
1
2
3
4
5
+30 more rows

The nested When conditions in the For loop evaluate the following questions:

Are there less than 100 rows in the 'Transactions' table?
Is the total number of records in the 'Transactions' table not a multiple of six?

Whenever both When conditions return a value of TRUE, a further seven records are generated and concatenated onto the existing ‘Transactions’ table.

The When conditions return a TRUE value five times. At that point there are a total of 36 rows of data in the ‘Transactions’ table.

When 36 rows of data are created in the 'Transactions' table, the second When statement returns a value of FALSE and therefore the load statement following this will no longer be executed.

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