Replace a nested if, with pick and match functions

Sometimes there is a need to do a big nested if-statement, check the value of one field, and then do something based on that value. For example:

if([Type] = 'Outstanding', [Outstanding Value], if([Type] =
Future ', [Future Value], if([Type] = 'Current',
[Current Value], [Other Value])))

This creates quite a hard to follow if-statement which may grow over time. By using the pick and match functions, we can simplify this like follows:

pick(1 + match([Type], 'Outstanding', 'Future', 'Current'), [Other Value], [Outstanding Value], [Future Value],
[Current Value])

By adding some line feeds we can make this easier to read:

pick(1 + match([Type],

'Outstanding',

'Future',

'Current'),

[Other Value],

[Outstanding Value],

[Future Value],

[Current Value])

Here's how it works:

QlikView help defines the pick function as follows:

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

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

For example:

pick( N, 'A', 'B', 4, , , )

returns 'B' if N = 2

returns 4 if N = 3

In our example, match is used to return the n:th integer by returning the position of the matching value in the list. Match returns 0 if no match is found. We can use this to create a default value by adding 1 to the match result and then putting the default expression first in the list of expressions.

So, when no match is found ([Type] is not Outstanding, Future or Current), the first parameter value for the pick is 1+0 and therefore the pick returns the value in [Other Value].

If the [Type]= ‘Future', match will return to 2 and the value for the first parameter in the pick will be 1+2, then the pick will return the value in [Future Value]