topic Re: How if nested works? in QlikView

How if nested works?

Thu, 21 Jul 2016 07:29:44 GMT

Hi!

I am using if nested, but I am having performance problems. I would like to know how does qlikview evaluate the conditions. I mean, if the first condition is true then the other "if" conditions are not evaluated? or do all "if" conditions are evaluated always?

For example:

if(a=b,1,if(a=c,if(a=d,1,0)))

desired behaviour:

If a=b -> True, then qlikview returns 1 and ignores the other "if" statements. Is this the real behaviour?

Please help me to understand!

Thank you.

Linda

Re: How if nested works?

MindaugasBacius — Thu, 21 Jul 2016 07:52:46 GMT

I suggest you painting a simple image:

Then follow up the path according your conditions.

Re: How if nested works?

marcus_sommer — Thu, 21 Jul 2016 07:55:50 GMT

Nested if-constructs are a performance-killer! I'm not absolutely sure but I believe that at first all branches of a nested-if-loop will be calculated and the evaluation which part is true comes at the last step.

Therefore is often better to use other techniques for this, for example the pick(match()) and/or alt or with a mapping or maybe set analysis. It will depend on your concrete scenario which (kind or combination of methods) suits best whereby in your case you could also use:

if(a=b or a=c or a=d, 1, 0)

or here another example:

alt(pick(match(a, 'b', 'c', 'd'), 1, 1, 1), 0)

- Marcus

Re: How if nested works?

PrashantSangle — Thu, 21 Jul 2016 08:04:00 GMT

Hi,

I have experience such issue.

Yes I agree with you, that first all branches of nested if else loop calculated and because of that we face so much performance issue.

Regards,

Re: How if nested works?

Thu, 21 Jul 2016 14:34:07 GMT

I see... Thank you Marcus... I have another question please, My if conditions are made with the following structure: if (a>0, 1, 0) ...how can I replicate it with pick/Match? , is it possible? ..or pick/match only works with equal "="?.

Thank you.

Re: How if nested works?

Thu, 21 Jul 2016 14:35:38 GMT

Thank you Max! ...how did you solve your problem?

Re: How if nested works?

marcus_sommer — Thu, 21 Jul 2016 15:57:53 GMT

Please give a more practical example and some more details to where do you want to use it with which aim (script/gui, more fields included, as flag, ...?).

- Marcus

Re: How if nested works?

PrashantSangle — Fri, 22 Jul 2016 04:57:06 GMT

Hi,

depends on requirement. Most of the time set analysis help me to resolve such issue.

Some time I have create flag in script or modify script.

Regards,

Re: How if nested works?

Thu, 04 Aug 2016 19:25:33 GMT

I am using it on a variable which is called and executed from an object in gui (indicator).

I have changed "if nested" into "or", but I still have performance problems:

Details:

$variable code:

if(

a>0 or

b>0 or

c>0 or

...(500 comparisons more)

z>0 ,1,0)

indicator expression:

if($variable>0,1,0)

I have 13 indicators with the same logic on a sheet. The problem is that the sheet takes 15 minutes to refresh and show the objects. (performance problem)

I am thinking that perhaps objects(indicator) does not support too much operations (execution of lines of code). What do you think?

Re: How if nested works?

marcus_sommer — Fri, 05 Aug 2016 06:54:42 GMT

I think an expression which makes more than 500 comparisations will be always perform very poorly. Therefore I think you will need a different approach by simplifying some of the parts by precalculating some parts within the script, creating clusters for your a,b,c ... and something more.

Like above mentioned you need to provide more details. If a,b,c are different fields or variables or expressions? Where should it be used - chart-type, textbox, .... which dimensions are included? What is the end-aim of it - delivering 0 or 1 is probably only an intermediate step?

- Marcus

Re: How if nested works?

PrashantSangle — Fri, 05 Aug 2016 07:11:41 GMT

Hi,

Another possible solution is if possible move all complex expression in script.

Create flag according to that and use flag in expression.

Regards,

Re: How if nested works?

jonathandienst — Fri, 05 Aug 2016 09:17:47 GMT

I agree that some refactoring of your model and/or performing the calculations in the back end are the best solution. But in the meantime, you could try restating the expression:

=If(RangeMax(a, b, c, d, ...., n) > 0, 1, 0)

Just fill in the RangeMax with all the values that you want to test for > 0. It may perform better than the if expression.

Re: How if nested works?

Fri, 05 Aug 2016 14:38:41 GMT

Hi Marcus,

a, b, c.. are expressions like this:

if(isnull($(vLI($1,'P027','E050'))) and isnull($(vLS($1,'P027','E050'))),0,

if(isnull($(vLI($1,'P027','E050'))),if($(vValor($1,'P027','E050')) <= $(vLS($1,'P027','E050')),0,1),

if(isnull($(vLS($1,'P027','E050'))),if($(vValor($1,'P027','E050')) >= $(vLI($1,'P027','E050')),0,1),

if($(vValor($1,'P027','E050')) >= $(vLI($1,'P027','E050')) and $(vValor($1,'P027','E050')) <= $(vLS($1,'P027','E050')),0,1))))

$vLI, $vLS and $vValor are variables that return me values from the data using set analysis and taking into account the parameters that I send to them.

$vLI -> returns me the limit inferior of the year that the user has selected in the filter.

$vLS -> returns me the limit superior of the year that the user has selected in the filter.

$vValor -> returns me the value that I want to compare with limits.

Functionality: I compare if a value is out from limits superior and inferior. If it is out, returns "1", if not returns "0".

The problem is that I need to compare 500 values. If at least one of them is out from limits my object (indicator) has to be painted red.

$variable code:

if(

a>0 or

b>0 or

c>0 or

...(500 comparisons more)

z>0 ,1,0)

indicator expression:

if($variable>0,1,0)

And it has to be dynamic, I mean, if the user select a date filter, the calculations have to be done at the moment, that's why I don't think I can put it within the script.

Another alternative that I have tested is to distribute the calculations in more than one indicator, it is faster but the problem is that too many objects on the sheet is still a performance problem.

That is the complete scenario. Hope you have any idea to improve the performance. Thank you!

Re: How if nested works?

Fri, 05 Aug 2016 14:48:22 GMT

Thank you Jonathan, I will test it.

Re: How if nested works?

Fri, 05 Aug 2016 14:50:06 GMT

Hi Max,

Thank you, as I explain to Marcus above, I don't think I can put the code within the script because it has to be dynamic, depending on a date filter that the user selects on the gui.

Re: How if nested works?

Fri, 05 Aug 2016 15:37:32 GMT

Hi Jonathan,

I have tested with RangeMax but it takes the same time to be calculated

Re: How if nested works?

marcus_sommer — Fri, 05 Aug 2016 15:51:39 GMT

I have the feeling that your approach is somehow overcomplicated and that it could be simplified in any way. Please provide a small example:

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

- Marcus