Pick Match with ‘greater/less than’ operators

Mon, 12 Sep 2016 08:06:41 GMT

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

Re: Pick Match with ‘greater/less than’ operators

sunny_talwar — Mon, 12 Sep 2016 16:58:23 GMT

Very cool!!! Thanks for sharing, would you be able to share a sample where it has been implemented? Would like to play around with it to see what it is doing.

Once again, thanks for sharing this.

Best,

Sunny

Re: Pick Match with ‘greater/less than’ operators

MarcoWedel — Mon, 12 Sep 2016 23:56:38 GMT

Hi,

maybe also possible:

=Pick(Match(Floor(expression,0.05),0.95,0.9)+1,$(vRed),$(vGreen),$(vAmber))

regards

Marco

Re: Pick Match with ‘greater/less than’ operators

mohan2391 — Fri, 18 Nov 2016 12:30:04 GMT

Very Nice..

Thanks for sharing..

topic Pick Match with ‘greater/less than’ operators in QlikView

Pick Match with ‘greater/less than’ operators

Re: Pick Match with ‘greater/less than’ operators

Re: Pick Match with ‘greater/less than’ operators

Re: Pick Match with ‘greater/less than’ operators