Equivalent to a switch case or match with numeric comparison

[Anonymous]

Hello Community,

I have been searching for hours a solution to an old reququest :

https://community.qlik.com/ideas/2563

In fact, I try to write a code which evaluates my variable only once (because it consumes a lot of time), in order to avoid to evaluates it for time in each IF statement.

My code is the following :

=IF(RangeMin($(vCouleurBAAA),$(vCouleurBABB),$(vCouleurBABD))>=0.9,ARGB(255,193,238,149),

  IF(RangeMin($(vCouleurBAAA),$(vCouleurBABB),$(vCouleurBABD))>=0.75,ARGB(255,255,255,153),

  IF(RangeMin($(vCouleurBAAA),$(vCouleurBABB),$(vCouleurBABD))>=0.5,ARGB(255,255,197,138),ARGB(255,255,119,122))))

I thought of different solutions, without success. I would like to write a code like this (one evaluation of the variable).

Switch(RangeMin($(vCouleurBAAA),$(vCouleurBABB),$(vCouleurBABD)))

     Case (>=0.9) :

          // code

     Case(>=0.75) :

          // code

     Case(>=0.50) :

          // code

     // etc.

End

Or something like a match with numeric comparison( greater than, less than etc.) in it likes :

match(RangeMin($(vCouleurBAAA),$(vCouleurBABB),$(vCouleurBABD)), >=0.9, >=0.75, >=0.5) // etc. and pick the right color

Thanks a lot !!

[vishsaggi]

May be check here?

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/ScriptControlStatement...

[rwunderlich]

Switch/Case is available only in script, not chart expressions. There are several solutions to the multi evaluation problem in a chart, depending on context.  I take it this is a background color expression in a chart?

If the expression:

RangeMin($(vCouleurBAAA),$(vCouleurBABB),$(vCouleurBABD))

is already being calculated in the chart, then refer to the expression label or the column() in your expression. For example, if this expression is in a column named "Sales" make your background color expression like:

if(Sales>0.9, etc...

If the expression is not already being calculated in the chart, add it is as hidden column in the chart and again refer to the label in the color expression.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[Anonymous]

Hello ! Thanks for yours answers guys.

Vishwarath, I need to use a "switch like" statement in my expression, not in my load script, but thanks

Rob, my expression is evaluated on the fly (not in a chart) and a lot of these expression are evaluated too. Something like 15 expressions are evaluated on the fly, in order to color buttons. These colors represent a level of risk in the app.

Because of the number of buttons, I can't create a hidden field for each, it's seems dirty to me and not very maintainable.

It's to bad that switch/case aren't available in chart expressions.

[rwunderlich · Accepted solution]

If you don't need to evaluate in a chart, you can define it and evaluate it once in a variable with "=". for example, create a variable named vMyExpr and define the contents as:

=RangeMin($(vCouleurBAAA),$(vCouleurBABB),$(vCouleurBABD))

This effectively makes references to vMyExpr a constant.

Then your button color expression will be:

if(vMyExpr>0.5, .....

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[Anonymous]

Hi Rob !

Like I said before, I have a lot of variable with a RangeMin(...) in it (like different variable). So I have to double my number of variable (with expression in it). I think it's not an optimal way beacause the qlikview app will be harder to maintain, but I have no other solution for now.

Thanks for your expertise