topic Aggr in Script in QlikView

Aggr in Script

Tue, 24 May 2011 16:58:42 GMT

Hi all, I have Pivot chart with one dimension Cyclic Group (Payer,Type,Product), and a calculated dimension,

=aggr(if((sum([Pay]) / sum([Clms])) = 0,'0',

if((sum([Pay]) / sum([Clms]))>0 and (sum([Pay])/ sum([Clms])) <= 19,'1-19',

if((sum([Pay]) / sum([Clms]))>19 and (sum([Pay]) / sum([Clms])) <= 29,'20-29',

if((sum([Pay]) / sum([Clms]))>29 and (sum([Pay]) / sum([Clms])) <= 39,'30-39',

if((sum([Pay]) / sum([Clms]))>39 and (sum([Pay]) / sum([Clms])) <= 59,'40-59',

if((sum([Pay]) / sum([aClms]))>59,'60+')))))),Payer,Type,Product)

and expression as Sum([Clms])

My charts works fine but its slow and most of the time its showing "Allocated memory exceeded" ... I am sure its because of the above calculated dimension.

Can someone please help me in how to implement this in Scrip rather than as chart expression.

Thanks in advance.

Aggr in Script

Wed, 25 May 2011 04:23:18 GMT

Try this:

Common_0:

Load

Pay,

Clms,

Payer,

Type,

Product

from Table1.qvd;

Common_2:

load

Payer,

Product,

Type,

Sum([Pay]) as PayTotal,

Sum([Clms]) as ClmTotal

resident Common_0 group by Payer,Product,Type;

drop table Common_0;

Test:

Load *,

if((PayTotal/ClmTotal)>0 and (PayTotal/ClmTotal) <= 19,'1-19',

resident Common_2;

drop table Common_2;