topic Re: Performance Issue : Another way to write this expression in QlikView

Performance Issue : Another way to write this expression

Fri, 15 Feb 2013 13:04:00 GMT

HI All,

I am having a ridiculous amount of trouble running this expression and can't think of another way of writing this and still getting the same result

aggr(count({$<CalendarMonthAndYear={'vMonthSpecific'},CallType={'o'},CallClassMapping={'Mobile'}>}Key),Digits, Extn, FirstName, Surname, CalendarMonthAndYear)

What the expresion does is the following

Counts the number of Keys with the set analysis above
Aggregates this over Digits, Extension, FirstName, Surname and CalendarMonthAndYear

Additional information

Dataset has been limited to 1 Month
800000 rows

Can someone explain or help me to get this expression to calculate without taking an hour

Thanks,

Byron

Re: Performance Issue : Another way to write this expression

Gysbert_Wassenaar — Fri, 15 Feb 2013 13:41:26 GMT

If vMonthSpecific is a variable then you probably don't need to aggregate over CalendarMonthAndYear since you're already limiting it to one value. If CalendarMonthAndYear is a date field you could make sure vMonthSpecific is a number and loose the single quotes. Comparing numbers is faster than comparing strings.

If you really want to improve performance, you might want to precalculate this in the script instead of in an expression. Something like:

PreCalculatedKeyCounts:

Load

Digits, Extn, FirstName, Surname, CalendarMonthAndYear, count(Key) as KeyCount

resident ...mydataset...

group by Digits, Extn, FirstName, Surname, CalendarMonthAndYear

where CallType = 'o' and CallClassMapping = 'Mobile';

Re: Performance Issue : Another way to write this expression

Mon, 18 Feb 2013 08:35:15 GMT

thanks for your reply mate. Removing the calendarmonthandyear did speed it up a little.

I think I will also take your suggestion about aggregating this in the script along with some other expressions which are causing a bit of trouble for me.

cheers,

byron