Conditional Functions

Let's talk about Conditional Functions that can be used in QlikView.

Load following script and let's analyse what happen to our data:

set conIF =             if              ($1>=5, 'ok', 'no');

set conAlt =            Alt             (Num($1), Num($2), 'nothing');

set conPick1 =          Pick            ($1, 1,2,3,4,5,6,7,8,9,10);

set conPick2 =          Pick            ($1, 1,2,3,4,50,6,7);

set conMatch =          Match           ($1, 1,2,3,4,5,6,7,8,9,10, 'A','B','C','a','b','cC','f');

set conMixMatch =       mixMatch        ($1, 1,2,3,4,5,6,7,8,9,10, 'A','B','C','a','b','cC','f');

set conWildMatch =      wildMatch       ($1, 1,2,3,4,5,6,7,8,9,10, 'A','B','C','a','b','cC','f*');



set conClass10 =        Class           ($1, 4);

set conClass11 =        Class           ($1, 5, 'Y');

set conClass21 =        Class           ($1, 5, 'Y', 1);

set conClass22 =        Class           ($1, 5, 'Y', 2);

set conClass23 =        Class           ($1, 5, 'Y', 3);



TestTable:

Load

*, 

$(conIF(Field1)) as sttIF, $(conAlt(Field1, Field2)) as sttAlt, $(conPick1(Field1)) as sttPick1, $(conPick2(Field1)) as sttPick2, 

$(conMatch(Field1)) as sttMatch, $(conMixMatch(Field1)) as sttMixMatch, $(conWildMatch(Field1)) as sttWildMatch, 

$(conClass10(Field1)) as sttClass10, $(conClass11(Field1)) as sttClass11,

$(conClass21(Field1)) as sttClass21, $(conClass22(Field1)) as sttClass22, $(conClass23(Field1)) as sttClass23



Inline [

Field1, Field2

0

1

2

3

4

5

6

7

8

9

10

a,1

b,2

c

A

B

C

foo

];

if(condition , then , else)

The three parameters condition, then and else are all expressions. The first one, condition, is interpreted logically. The two other ones, then and else, can be of any type. They should preferably be of the same type. If condition is true, the function returns the value of the expression then. If condition is false, the function returns the value of the expression else. 

if(Field1>=5, 'ok', 'no')

Under red square we see  lines where Field1>=5
I'd like to underline that Field1 has mixed data (numeric and string), so we may see lines under green square

alt (case1[ , case2 , case3 , ...] , else)
The Alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.

Alt(Num(Field1), Num(Field2), 'nothing');

The first condition Field1 has numeric data (red square), if no - the second condition Field2 has numeric data (orange square), if no - 'nothing' (yellow square). 

As you see Alt function is analog of IIF statement.

   

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

Returns the n:th expression in the list. n is an integer between 1 and N.

set conPick1 = Pick (Field1, 1,2,3,4,5,6,7,8,9,10);
set conPick2 = Pick (Field1, 1,2,3,4,50,6,7);

It's not difficult to see that pick works like switch ... case statement.

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

The match function performs a case sensitive comparison

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

The mixmatch function performs a case insensitive comparison.

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

The wildmatch function performs a case insensitive comparison and permits the use of wildcard characters ( * and ?) in the comparison strings. 

set conMatch = Match(Field1, 1,2,3,4,5,6,7,8,9,10, 'A','B','C','a','b','cC','f');
set conMixMatch = mixMatch(Field1, 1,2,3,4,5,6,7,8,9,10, 'A','B','C','a','b','cC','f');
set conWildMatch = wildMatch(Field1, 1,2,3,4,5,6,7,8,9,10, 'A','B','C','a','b','cC','f*');

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

Creates a classification of expressions. The bin width is determined by the number set as interval. The result is shown as a<=x<b, where a and b are the upper and lower limits of the bin. The x can be replaced by an arbitrary string stated in label. 0 is normally the default starting point of the classification. This can be changed by adding an offset.

set conClass10 = Class(Field1, 4);
set conClass11 = Class(Field1, 5, 'Y');
set conClass21 = Class(Field1, 5, 'Y', 1);
set conClass22 = Class(Field1, 5, 'Y', 2);
set conClass23 = Class(Field1, 5, 'Y', 3);

Class10 grouped by 4 lines;
Class11 grouped by 5 lines;

Class21 grouped by 5 lines and shifted by 1;
Class22 grouped by 5 lines and shifted by 2;
Class23 grouped by 5 lines and shifted by 3;

Copyright © 2015 Ruslan Goncharov