topic Re: Set Analysis with OR Condition - performance in QlikView

Set Analysis with OR Condition - performance

settu_periasamy — Wed, 08 Jul 2015 05:34:41 GMT

Hi All,

I have the set analysis expression like the below using OR Conditon.

There is no problem in this expression, but the response time is very high (it takes almost 5 mins to display the result )

i'm having more than 10 millions of records.

Count({1}{<Table={'101','100'},Type={'Fixed','List'},VALFROM={">$(vFrom)"}>

<Table={'101','100'},Type={'Fixed','List'},VALTO={"<$(vTo)"}Product)

At the same time, i tried to separate this expression like,

Count({1}{<Table={'101','100'},Type={'Fixed','List'},VALFROM={">$(vFrom)"}>}Product)

Count({1}{<Table={'101','100'},Type={'Fixed','List'},VALTO={"<$(vTo)"}Product)

I get the immediate result for above expressions.

Could anyone give the suggestion to improve the performance for this?

Edit:

I tried with if condition like

if(VALFROM>$(vFrom) or VALTO<$(vTo),

Count({1}{<Table={'101','100'},Type={'Fixed','List'}>}Product) )

This is bit increase the response time compare with OR in set analysis.

So, Just i ask you, shall i use the if condition? or do you have any idea to do this in set expression with OR ..

Message was edited by: Settu Periasamy

Re: Set Analysis with OR Condition - performance

Siva_Sankar — Wed, 08 Jul 2015 06:02:34 GMT

Settu,

Try using flags in the script level and use those flags in your expression. Refer Optimize UI Performance with Flags and Set Analysis

Re: Set Analysis with OR Condition - performance

vikasmahajan — Wed, 08 Jul 2015 06:07:57 GMT

While Loading data you can create flags and use this flags in set anlysis will improve the performance and effetive coding as per shankarece link.

Vikas

Re: Set Analysis with OR Condition - performance

jonathandienst — Wed, 08 Jul 2015 06:14:16 GMT

I assume you are trying to get records between a pair of dates. Your expression is using a union, and so very likely it is trying to return everything from the table. Corrected syntax would be:

=Count({1<Table={'101','100'}, Type={'Fixed','List'}, VALFROM={">$(=vFrom)"}, VALTO={"<$(=vTo)"}>} Product)

But are you sure that you want to ignore all selections in this expression? Perhaps you need it without the '1'

=Count({<Table={'101','100'}, Type={'Fixed','List'}, VALFROM={">$(=vFrom)"}, VALTO={"<$(=vTo)"}>} Product)

If you want to do it using 2 set expressions, you need an intersection like

Count({1<Table={'101','100'}, Type={'Fixed','List'}, VALFROM={">$(=vFrom)"}> *

1<Table={'101','100'}, Type={'Fixed','List'}, VALTO={"<$(=vTo)"}>} Product)

(Note the 1 applied to both expressions), or:

Count({<Table={'101','100'}, Type={'Fixed','List'}, VALFROM={">$(=vFrom)"}> *

<Table={'101','100'}, Type={'Fixed','List'}, VALTO={"<$(=vTo)"}>} Product)

But these are functionally the same as the simpler expressions above.

Re: Set Analysis with OR Condition - performance

settu_periasamy — Wed, 08 Jul 2015 06:40:29 GMT

Hi Jonathan,

Thanks for your time.

i'm not trying to get the records between of pair of dates. Actually i need to display the records, if VALFROM > somedate(eg. 2020-01-01) OR if VALTO < '2000-12-31'.

My expression gives the expected result. But the response time is very high.

Thank you for your suggestion.

Re: Set Analysis with OR Condition - performance

settu_periasamy — Wed, 08 Jul 2015 06:40:42 GMT

Thank you Vikas.

Re: Set Analysis with OR Condition - performance

settu_periasamy — Wed, 08 Jul 2015 06:40:55 GMT

Thank you Shankar.

Re: Set Analysis with OR Condition - performance

jagan — Wed, 08 Jul 2015 06:49:28 GMT

Hi,

Move this into set analysis like below, set analysis will be faster.

Sum({<VALFROM={'>$(=vDate1)'} >+ <VALTO = {'<$(=vDate2)'} >} Value)

Assume that vDate1 and vDate2 are variables holding the dates.

Regards,

Jagan.

Re: Set Analysis with OR Condition - performance

settu_periasamy — Wed, 08 Jul 2015 07:03:33 GMT

Hi Jagan,

Already i tried this (i have used Count the product instead of sum). But the response time is very very slow.

that's why , i moved from set expression to IF condition.

if(VALFROM>$(vFrom) or VALTO<$(vTo),

Count({1}{<Table={'101','100'},Type={'Fixed','List'}>}Product) )

- This expression gives the better performance compare with Set expression.

Thanks Jagan.

Regards,

Settu P

Re: Set Analysis with OR Condition - performance

jagan — Wed, 08 Jul 2015 07:09:08 GMT

Hi,

It should not be like that, if those dates are fixed or able to arrive a flag in the script, then create a flag and this in Set analysis.

Hope this helps you.

Regards,

Jagan.