aggr function performance problem

[#1]

Hi,

I am hitting a strange performance issue with the app  I am working on and can not figure out why qlikview is performing the way it does. I have attached a sample application with a slice of my data. I have only included one expression in the sample app (the one with the issue).

Things start to get slow when I reload about 3M rows. By the time I load more data (~5M rows), the app just hangs  on the chart  (displaying the progress bar on the chart)  while calculating the following expression

=count(aggr(memb_id,memb_id, service_date_id, service_type))

when the app freezes, windows stops responding too and the only way out is a hard reboot. I am running this on a laptop with 8gig of ram, however, when i upload the app to our server (96gig memory) same problem is observed.

Please take a look and maybe someone can spot an issue. Does the aggr function just rolls over for larger data sets? I have used the aggr function in other apps but never have seen this qlikview perform like this.

[#2]

Thanks Rob for your help. Yes, this alternative expression runs much faster. My app does not freeze and it actually completes the calculation now which is a step in the right direction. However, depending on how much data is sliced by the user from the dim filters, it could take up to a minute or longer to complete.

Do you, or any one else on this forum has other ideas I could build on this?

to reiterate again what the chart is suppose to do:

it is suppose to count and return number of groups in the data set expressed by the following expression:

Count(DISTINCT memb_id & service_date_id & service_type)

[jagan · Accepted solution]

Hi,

Try like this in script

Data:

load

autonumberhash256( memb_id & '_' &  service_date_id & '_' & service_type)  AS Key

memb_id,

service_date_id,

year,

  month,

service_type,

amount,

cust_id,

location_type,

age,

cust_type

from

mydata.qvd

(qvd) where service_type  = '97001';

KeyCount:

LOAD

DISTINCT Key,

1 AS KeyCount

RESIDENT Data;

Now you can use Sum(KeyCount) as expression in your charts to calculate the key count.

Hope this helps you.

Regards,

Jagan.

[#4]

Very clever solution. The worst calculation now takes less than 5 seconds which is acceptable.

Thanks for your reply.