

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