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;
