topic simplify SetAnalysis expression in QlikView

simplify SetAnalysis expression

Mon, 17 Jan 2011 12:43:25 GMT

Hi, i have this "huge" and specialy slowly expression:

if(count({<PruebaFami={"Tiene"}>} TipoAviso)<3,
if(count({1<Terminal>} Terminal)=1,count({<PruebaFami={"Tiene"}>} TipoAviso),
if(count({<PruebaFami={"Tiene"}>} TipoAviso)<2,count({<PruebaFami={"Tiene"}>} TipoAviso))))

When accept it, qlikview cannot show the results, the system hangs and i need to close the Qv.exe process using Ctrl-Alt-Sup.

What i need is to get the count of TipoAviso that have every Terminal, but with some conditions:
- Firstly i only need to show results if count of TipoAviso < 3 and PruebaFami = "Tiene".
- Then if the count of Terminal (Terminals with same value) = 1, must appear the count results of each Terminal.
- Otherwise (when the count of Terminals <> 1, what means it >1), for these cases i only need the results when the count of TipoAviso = "Tiene" < 2.

how to do it simpler and effective?

I hope your help, please.
Regards

simplify SetAnalysis expression

Mon, 17 Jan 2011 16:12:46 GMT

I have added and example to help find the solution.

Regards

simplify SetAnalysis expression

jonathandienst — Wed, 19 Jan 2011 16:30:59 GMT

Firstly, I would check the data structure. If PreubaFami, TipoAviso and/or Terminal (and indeed the chart/table dimensions) are in large tables that are not linked to each other, this would result in a memory-gobbling cross join, which could be your problem.

If that is not the case, you might get some joy by defining a variable in the script, for example:

Set vCountTiene = count({<PruebaFami={"Tiene"}>} TipoAviso)

and then using $(vCountTiene) in your expression instead, like:

if($(vCountTiene) < 3,
if(count({1<Terminal>} Terminal)=1, $(vCountTiene),
if($(vCountTiene) < 2, $(vCountTiene))))

I have seen some discussions that imply that QV does some caching of expressions in variables. It was in a different context and may or may not work here.

Just some thoughts which I hope are helpful.

Jonathan