topic Re: Optomize select in field using AGGR and triggers? in QlikView

Optomize select in field using AGGR and triggers?

phcaptjim — Wed, 09 Dec 2015 03:42:55 GMT

I have an application where the user can enter the minimum number of admissions per patient and based on a variable and trigger, the app will select the patient ID's that meet the criteria. Once the variable is set, a variable trigger is executed to select patients. Here is the expression that does the selection using the select in field action:

='(' & Concat({<[Patient ID]=>}if(aggr(sum({<[Patient ID]=>} Admission_Flag),[Patient ID])>= v_Admissions, [Patient ID]), '|') & ')'

v_Admissions is the variable that the user is entering.

Is there an easier way to do this selection? I have about 750k patients in this application and it takes a little bit for this selection to happen. The aggr must happen on the fly since the user could filter on other dimensions such as payor, location, date, etc. Any ideas?

Thanks!

Re: Optomize select in field using AGGR and triggers?

tresB — Wed, 09 Dec 2015 04:36:04 GMT

So, is the issue only with performance that you are guessing because of aggr() ? Try to share a sample qvw with representative data to work on.

Re: Optomize select in field using AGGR and triggers?

marcus_sommer — Wed, 09 Dec 2015 12:41:21 GMT

You could try it without the concat() only with the search-expression as condition:

='=aggr(sum({<[Patient ID]=>} Admission_Flag),[Patient ID])>= v_Admissions'

Re: Optomize select in field using AGGR and triggers?

phcaptjim — Tue, 29 Mar 2016 17:11:19 GMT

Thanks for the replies. I'm finally getting back to this.

I did two things, first I took the suggestion that Marcus gave for the search-expression and then I created a table in my data model that just has hospital admissions (instead of searching the entire fact table). With those two changes the selections now occur immediately instead of waiting 4-6 seconds for the selections to be made. This should really make our users happy.