topic Re: Performance optimization of Expression in QlikView

Performance optimization of Expression

Wed, 23 Aug 2017 13:37:14 GMT

Hello All,

In my app I am selecting Prescriber using the below expression in a list box and on Map.

This is a trigger condition on a button :

=concat(aggr(if(sum(Volume)>20, only(Prescriber)),Prescriber),'|','')

However, the selection is taking more than a minute if number of prescribers is high(e.g >10000)

Any idea how we can optimize above scenario?

Thanks

Khushboo

Re: Performance optimization of Expression

sunny_talwar — Wed, 23 Aug 2017 13:53:33 GMT

Is this needed to be aggregated on the front end (in other words does this need to work with selections)? If it doesn't then may be pre-aggregate this in the script and then create a flag for each Precriber and then select the flag field...

Re: Performance optimization of Expression

Thu, 24 Aug 2017 06:23:05 GMT

Hi Sunny,

This needs to be aggregated on the front end.

sum(Volume)>20, is user input field. User may select some other metric or combination of metrics.

Hence, this cannot be calculated from script side.

Regards

Khushboo

Re: Performance optimization of Expression

tresB — Thu, 24 Aug 2017 06:52:32 GMT

Try this:

='(' & concat(Distinct {<Prescriber={"=sum(Volume)>20"}>} Prescriber ,'|') &')'

Edit: corrected a bracket.

Re: Performance optimization of Expression

kaushiknsolanki — Thu, 24 Aug 2017 07:03:42 GMT

Hi,

I feel you dont need any trigger.

All you do if add a filter i.e Prescriber and ask your user to search on that field as =sum(Volume)>20

it will do the relevant selection.

give it a try.

Regards,

Kaushik Solanki

Re: Performance optimization of Expression

Thu, 24 Aug 2017 10:50:23 GMT

Thanks Trsesco for the solution.

Now in my actual implementation,sum(Volume)>20 is something which the user is selecting on the fly.

This is coming from a var: vExp

='(' & concat(Distinct {<Prescriber={"=vExp"}>} Prescriber ,'|') &')'

When i try this prescribers are coming as blank.

Am i doing anything wrong in above expression. Is there anyway to correct the same.

Re: Performance optimization of Expression

tresB — Thu, 24 Aug 2017 10:57:10 GMT

Try with $ expansion like:

='(' & concat(Distinct {<Prescriber={"=$(vExp)"}>} Prescriber ,'|') &')'