topic Maybe Set Analysis? in QlikView

Maybe Set Analysis?

Mon, 19 Sep 2011 01:54:34 GMT

I have a problem where an expression takes a long time to refresh when new selections are made. I have tried converting the expression with Set Analysis, but for some reason it does not give me a result. The expression is: Count(Distinct(IF(Trim(field1) = 'Some Condition', field2))). My second part of the question is: Is it Best Practise to use Set Analysis to speed up expression calculations or should I rather be doing the "complex" calculations in the load script?

Maybe Set Analysis?

kaushiknsolanki — Mon, 19 Sep 2011 05:06:54 GMT

Hi,

Let me tell you one thing, that Count() function is a time intensive, meaning it takes more time to execute then other functions.

In you case it takes much longer time cause with count you have if statement.

The equivelent set expression for your expression is.

Count({<Field1 = {"Some Condition"}>}distinct Field2).

This may take time too... as its a count function.

The best practise is to put an extra field to table. say '1'.

Now you can use the sum function on these field, and get count.

Regards,

Kaushik Solanki

Maybe Set Analysis?

Wed, 21 Sep 2011 01:24:06 GMT

Hi Kaushik - That was a good tip creating a new field: i.e. 1 AS [myCounter] and doing a Sum on it rather than using the Count clause. I also did the Trim part in the back-end and the refresh rate is now milli-seconds again apposed to 30 seconds - as it was when all the calculations and the Count was done in the expression on the front-end.

Maybe Set Analysis?

kaushiknsolanki — Wed, 21 Sep 2011 04:53:15 GMT

Hi,

Thats good. From next time you wont get performance issue for sure - as you got the solution for that.

Regards,

Kaushik Solanki