topic Re: Performance: PICK vs. nested IF in QlikView

Performance: PICK vs. nested IF

datanibbler — Fri, 18 Oct 2013 20:24:58 GMT

Hi,

I have just read in my book "QlikView 11 for Developers" as I regularly do, there's always some detail that I have not used before.

This time, it is the PICK function. The book uses the interesting phrase that it "...can even be lighter..." (than a nested IF).

Every one of my apps has, on the front_page, a diagram with the names of all the KPIs contained in that app and the YTD value.

In some of my new apps, there are 15 KPI diagrams, so in the front_page_diagram, there are 15 lines with 2 formulas each:

- a YTD value of the KPI

- a status_light (gauge chart) that uses that formula again to determine whether the light should be red or green.

=> That makes for two quite complex nested IF formulas.

Up to now we have not had any performance problems as all the apps are run on the server - but going forward the general load on the server is certainly going to increase, so everything I can do to keep the load as light as possible along the way will help us.

Can anyone tell me if it would be worthwhile, in these formula-heavy diagrams, to replace the entire nested IF_construct with a PICK function?

Thanks a lot!

Best regards,

DataNibbler

Re: Performance: PICK vs. nested IF

tresB — Sat, 19 Oct 2013 08:26:21 GMT

I guess you are talking about the use of combination of pick() and match() like: pick(match() ,....). If it is so, then yes, it can substantially improve app's front-end performance. It somehow works like 'Case' in scripting. The same you can experiment with a big app.

Re: Performance: PICK vs. nested IF

datanibbler — Mon, 21 Oct 2013 07:23:58 GMT

Hi tresesco,

no, I would not be using match() (at least I don't think so): My formula now is like

>>> If(line=1, [expr1], IF(line=2, [expr2])) <<<

The other one (for the status) is still more complex, it is like

>>> IF(line=1, IF([expr1], 1, -1), IF(line=2, IF([expr2], 1, -1)) <<<

(both with more lines, of course.)

I would replace that (will that work when >line< is not fix?) by

>>> pick(line, [expr1] [expr2]) <<<

(only with more lines).

I guess it can still improve performance.

As I said, up to now we have not had any problems with that (except one that was due to a formula) - the "live" Apps are calculated on the server and viewed by the employees via IE, so that diminishes the problem.

Still, as the load is going to increase anyway, keeping it low along the way seems like a worthwhile thing to do.

Thanks a lot!

Best regards,

DataNibbler

Re: Performance: PICK vs. nested IF

datanibbler — Mon, 21 Oct 2013 09:49:26 GMT

Hi tresesco,

this moderating thing takes quite a while.

You are right. I do need match(). I just looked up its correct syntax in another app where I have it, thanks to someone here in the Community ;-).

So I must give the MATCH() fct. all the possible values - nrs from 1 to 15 - but that is little effort.

I have introduced a comment line inbetween every two expressions now to make it perfectly clear which line that formula goes into. That way, it all stays manageable.

Thanks a lot!

Best regards,

DataNibbler