Recently when I was developing one of my QV applications I faced a problem of using Pick Match functions in situation where we have to link values to some separate ranges. Of course we can always use a complex IF structure, but it was not something I would be happy about. I haven’t found a solution on community so together with my Team Mate we have figure out a way how to do it with Pick Match.
When we have just a simple query where we need to distinguish between values greater/less /equal 0, we can use the function sign(), below is the sample example, to assign the color depending on the expression value:
=Pick(Match(sign(expression),-1,0,1),lightred(),black(),green())
More complex case will be where we will need to distinguish between some ranges with only positive values (or negative, values which are not only different due to its sign). My problem was to assign a color to an indicator depending on its value, so green where we are between 0.95-0.1, amber 0.9-0.95, and red for 0.0-0.9. The way to describe such statement using Pick Match function, is to combine it with class function, example below:
=Pick(Match(class(expression,0.05),class(0.95,0.05),class(0.9,0.05))+1,$(vRed),$(vGreen),$(vAmber))
This way we can avoid using complex IF statement and stick with Pick Match functions which are a lot faster. Above are only just sample examples, but I believe that Qlik is giving us the opportunity to use it in more complex cases as well.
Hope you enjoy,
Maciej Borkowski
Message was edited by: Maciej Borkowski
Simple example attached.
Please forgive me the ugly colours, but just wanted to show how you may use it in yours applications:)
Regards,
MB
