topic Re: Optimize AGGR() function inside a trigger in QlikView

Optimize AGGR() function inside a trigger

phcaptjim — Fri, 23 May 2014 14:15:57 GMT

I've got a few variable input boxes that allows the user to filter patients in a healthcare application.

One filter is for # of admissions. The field Admission_Flag is either 1 or 0, and the variable v_Admissions is the number of admissions entered into the input box. When a value is entered, this expression is executed and MRN numbers are selected. This runs slow and I would like to improve the performance. Any ideas?

='(' & Concat({<[Patient MRN]=,PayorName=,[Payor GL]=,[Payor Level 1]=,[Payor Level 2]=,[Payor Level 3]=>}if(aggr(sum({<[Patient MRN]=,PayorName=,[Payor GL]=,[Payor Level 1]=,[Payor Level 2]=,[Payor Level 3]=>}

Admission_Flag),[Patient MRN])>= v_Admissions, [Patient MRN]), '|') & ')'

Thanks!

Re: Optimize AGGR() function inside a trigger

whiteline — Fri, 23 May 2014 14:36:21 GMT

Try this:

='(' & Concat({<[Patient MRN]={'=sum({<[Patient MRN]=,PayorName=,[Payor GL]=,[Payor Level 1]=,[Payor Level 2]=,[Payor Level 3]=>}Admission_Flag)>=$(v_Admissions)'},PayorName=,[Payor GL]=,[Payor Level 1]=,[Payor Level 2]=,[Payor Level 3]=>} [Patient MRN]), '|') & ')'

Re: Optimize AGGR() function inside a trigger

phcaptjim — Fri, 23 May 2014 14:48:28 GMT

Whiteline, thanks for the reponse. I'm not sure why, but I am unable to get your expression to make any selections on [Patient MRN].

Re: Optimize AGGR() function inside a trigger

whiteline — Fri, 23 May 2014 16:45:58 GMT

It's not clear what you're trying to achieve.

I thought it's the point since you reset the selections in your original expression.

Remove the [Patient MRN]=, under the sum() set analysis.

Re: Optimize AGGR() function inside a trigger

phcaptjim — Fri, 23 May 2014 16:54:41 GMT

If the user enters admissions >= 2, then decided to change that to >=1, the selections have to be reset, so in the expression I am saying [Patient MRN]= so the current selection does not affect the selection that is being made. This is a bit complicated and I'm probably not explaining it as best as I can. Hopefully this helps.

Re: Optimize AGGR() function inside a trigger

whiteline — Fri, 23 May 2014 18:58:08 GMT

Could you upload brief sample ?

Re: Re: Optimize AGGR() function inside a trigger

phcaptjim — Wed, 28 May 2014 03:03:48 GMT

I've attached a very simple example. Please let me know what questions you might have.

This works really quick on a small data set.

Re: Optimize AGGR() function inside a trigger

perumal_41 — Wed, 28 May 2014 05:37:30 GMT

Please use below expression in test Application

='(' &

Concat({<PAT_MRN=,PAT_MRN={"=sum({<[PAT_MRN]=>}ADMIT_FLAG))>= v_Admissions"}>} [PAT_MRN], '|') & ')'

Regards,

Perumal A

Re: Optimize AGGR() function inside a trigger

phcaptjim — Wed, 28 May 2014 11:42:00 GMT

Thanks for the response Perumal.

When I apply that expression to the variable trigger (both on input and change) there are no selections made to PAT_MRN. The ADMIT_FLAG has to be summed by MRN to select those who are greater than or equal to the input field (v_Admissions).

Re: Re: Optimize AGGR() function inside a trigger

perumal_41 — Wed, 28 May 2014 12:04:55 GMT

For me wroking .PFA sample application.

Re: Optimize AGGR() function inside a trigger

phcaptjim — Wed, 28 May 2014 12:22:59 GMT

I believe it's using the trigger for OnChange instead of OnInput. The OnChange trigger still has my original AGGR expression. They both have to changed for the app to function properly.

Thanks!

Re: Re: Optimize AGGR() function inside a trigger

perumal_41 — Thu, 29 May 2014 09:35:14 GMT

Now is working .PFA sample application.

Re: Optimize AGGR() function inside a trigger

phcaptjim — Fri, 06 Jun 2014 01:52:14 GMT

I was able to use that expression in my application and the results were correct. However, the performance seemed about the same. I'm not sure if anything can be done to speed up this operation. The application has to select thousands of MRN #'s when you make an entry in the input box. Perhaps this is the nature of the beast?

For example, if I filter on a certain visit type, the app needs to select 200k MRN #'s. I'm not sure how to speed up that operation. We could aggregate these numbers in the load script, but then we would loose the ability to aggr over a certain period of time.

I appreciate the feedback on this thread. If anyone has other ideas, I would love to hear them.