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.

[MK_QSL]

Change the expression as below

=SUM(Aggr(COUNT(memb_id),service_date_id,service_type))

[terezagr]

Try to change the order of Aggr and Count in your expression like this:

Aggr(Count(memb_id),service_date_id,service_type)

[terezagr]

More about Aggr function and how to build it is here: Aggr() function

[#5]

But these two aggr function are not the same.

My expression:

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

groups the records by the three dims and then provides a count of the groups (that's what I am looking for).

Your expression:

SUM(Aggr(COUNT(memb_id),service_date_id,service_type)

counts number of rows in each group and then gives me the sum of those counts, it essentially gives me the total number of rows in the data set which could be calculated simply by count(memb_id) as you can see in the attached qvw file.

[#6]

The expression you provided is incorrect and what it produces does not really need an aggr function. Please see my response to the previous poster.

[MK_QSL]

Dont understand the logic behind keeping same field twoce in aggr function...

[rwunderlich]

I'm not sure aggr() is the best approach to get this count. If you want the count of groupings, an expression that will probably run much faster is:

=Count(DISTINCT memb_id & service_date_id & service_type)

-Rob

http://masterssummit.com

http://robwunderlich.com

[#9]

my aggr expression is equivalent to this sql:

select count(*) from (select memb_id from sometable group by memb_id, service_date_id, service_type) as a

the inner select is the aggr function I have and the outer select is like the outer count around the aggr function.

[rwunderlich]

Another way to write this in SQL would be:

select count(DISTINCT memb_id || service_date_id ||  service_type) from sometable

That is equivalent to the count(DISTINCT...) suggestion I made below.

-Rob