Skip to content

Conditional functions

The conditional functions all evaluate a condition and then return different answers depending on the condition value.

alt

The alt function returns the first of the parameters that has a valid number representation. If no such match i found, the last parameter will be returned. Any number of parameters can be used.

alt(expr1 [, expr2 , expr3 , ...] , else)

Argument Description
expr1 The first expression to check for a valid number representation.
expr2 The second expression to check for a valid number representation.
expr3 The third expression to check for a valid number representation.
else Value to return if none of the previous parameters has a valid number representation.

The alt function is often used with number or date interpretation functions. This way, Qlik associative engine can test different date formats in a prioritized order. It can also be used to handle NULL values in numerical expressions.

Examples and results:

Example Result
alt( date#( dat , 'YYYY/MM/DD' ),
date#( dat , 'MM/DD/YYYY' ),
date#( dat , 'MM/DD/YY' ),
'No valid date' )
This expression will test if the field date contains a date according to any of the three specified date formats. If so, it will return a dual value containing the original string and a valid number representation of a date. If no match is found, the text 'No valid date' will be returned (without any valid number representation).

alt(Sales,0) + alt(Margin,0)

This expression adds the fields Sales and Margin, replacing any missing value (NULL) with a 0.

class

The class function assigns the first parameter to a class interval. The result is a dual value with a<=x<b as the textual value, where a and b are the upper and lower limits of the bin, and the lower bound as numeric value.

class(expression, interval [, label [, offset ]])

Argument Description
interval A number that specifies the bin width.
label An arbitrary string that can replace the 'x' in the result text.
offset A number that can be used as offset from the default starting point of the classification. The default starting point is normally 0.
Example Result
class( var,10 ) with var = 23 returns '20<=x<30'
class( var,5,'value' ) with var = 23 returns '20<= value <25'
class( var,10,'x',5 ) with var = 23 returns '15<=x<25'

Example data load script:

In this example, we load a table containing name and age of people. We want to add a field that classifies each person according to an age group with a ten year interval. The source table looks like this:

Name Age
John 25
Karen 42
Yoshi 53

To add the age group classification field, you can add a preceding load statement using the class function. In this example, we load the source table using inline data.

LOAD *,
class(Age, 10, 'age') As Agegroup;

LOAD * INLINE
[ Age, Name
25, John
42, Karen
53, Yoshi];

The resulting data that is loaded looks like this:

Name Age Agegroup
John 25 20 <= age < 30
Karen 42 40 <= age < 50
Yoshi 53 50 <= age < 60

if

The if function returns a value depending on whether the condition provided with the function evaluates as True or False.

if(condition, then , else)

The if function has three parameters, condition, then and else, which are all expressions. The two other ones, then and else, can be of any type.

Argument Description
condition Expression that is interpreted logically.
then Expression that can be of any type. If the expression.
else Expression that can be of any type. If the condition is False, then the if function returns the value of the else expression.
Example Result
if( Amount>= 0, 'OK', 'Alarm' ) This expression will test if the amount is a positive number (0 or larger) and return 'OK' if it is. If the amount is less than 0, 'Alarm' is returned.

match

The match function compares the first parameter with all the following ones and returns the number of expression that matches. The comparison is case sensitive.

match( str, expr1 [ , expr2,...exprN] )

Note

If you want to use case insensitive comparison, use the mixmatch function. If you want to use case insensitive comparison and wildcards, use the wildmatch function.

Examples and results:

Example Result
match( M, 'Jan','Feb','Mar') returns 2 if M = Feb
returns 0 if M = Aprorjan

mixmatch

The mixmatch function compares the first parameter with all the following ones and returns the number of expressions that match. The comparison is case insensitive.

mixmatch( str, expr1 [ , expr2,...exprN] )

Note

If you want to use case sensitive comparison, use the match function. If you want to use case insensitive comparison and wildcards, use the wildmatch function.

Example Result
mixmatch( M, 'Jan','Feb','Mar') returns 1 if M = jan

pick

The pick function returns the n:th expression in the list.

pick(n, expr1[ , expr2,...exprN])

Argument Description
n n is an integer between 1 and N.
Example Result
pick(N, 'A', 'B', 4, 6) returns 'B' if N = 2
returns 4 if N = 3

wildmatch

The wildmatch function compares the first parameter with all the following ones and returns the number of expression that matches. It permits the use of wildcard characters(* and ?) in the comparison strings. The comparison is case insensitive.

wildmatch( str, expr1 [ , expr2,...exprN] )

Note

If you want to use comparison without wildcards, use the match or mixmatch functions.

Example Result
wildmatch( M, 'ja*','fe?','mar') returns 1 if M = January
returns 2 if M = fex