Saturday, 12 May 2012

In Statements in Qlikview - Match()

I don't know about everyone else but I have often googled for how to do an in statement in Qlikview.


#Note by an In statement I am referring to how to test one value against multiple values or a range of values


As searching for the word "in" in relation to Qlikvew is nigh on impossible here's a quick post explaining how to do it and how to use it for custom sort lists:



Match()

The match formula in Qlikview is great, although the first couple of times I've used it I had to scratch my head for a minute.

How to write it

A match statement is made up of three parts the Match, the value/field you want to test and the values you want to match against:

=Match(Field ,'Value 1' ,'Value 2','Value3')

This is a True/False statement so will return a 1 or 0 result.

How to use it

I use it in if statements all the time:

=If(Match(Field ,'Value 1' ,'Value 2','Value3'),'Value Found','Value Not Found')

On your chart, click the sort tab, check expression
and open the formula box

Protip: You can also you use it to drive your sort function within charts.













Using the formula below you can drive whether unlisted entries appear before or after your listed values.

Only use one of these lines of code, using them
together will result in some curious sorting!