topic Re: Fix memory issue - move calcs to script?? in QlikView

Fix memory issue - move calcs to script??

Fri, 07 Sep 2012 13:00:57 GMT

Hi All,

I am working on a series of Straight Table charts which contain a number of regular dimensions, calculated dimensions and expressions.

The problem is that I am facing some serious performance issues at the moment where the app grinds to a halt. When I try to add any additional calculations I am getting an 'Out of object memory' message.

I would appreciate some guidance as to how to resolve this. My first thought is that I should move these calculations into the script to improve performance - does this sound sensible?

If this is the way to go, then the next challenge that I face is the fact that the various fields that I need to use are on different tables. As I will need to complete my calculations between the 'Load' and 'From' - how do I efficiently bring in fields from other tables for this purpose?

Is there a better way to handle these calculations other then within Load & From?

Should I be using a Join (and dropping original tables?), Lookup or other? What are the main pros/cons?

Many thanks,

Alan

Re: Fix memory issue - move calcs to script??

danielrozental — Fri, 07 Sep 2012 16:29:59 GMT

You shouldnt be doing any calculated dimensions (that's almost never the best solution).

Post your expressions/calculated dimensions.

Re: Fix memory issue - move calcs to script??

Fri, 07 Sep 2012 17:11:42 GMT

Hi Daniel,

Thanks for the advice. Good to know to avoid calculated dimensions in the future!

Excuse my ignorance, but why are they such a bad choice?

As requested, the Expressions are listed below:

count(if(CategoryDesc='Financial' and Open_Clos='Closed', ID, NULL))

sum(if(CategoryDesc='Financial', PercentComp/100, NULL))

count(if(CategoryDesc<>'Financial' and Open_Clos='Closed', ID, NULL))

sum(if(CategoryDesc<>'Financial', PercentComp/100, NULL))

count(if(CategoryDesc='Financial' and Open_Clos='Closed' and PercentComp<>100, 1-(PercentComp/100), NULL))

sum(if(CategoryDesc='Financial' and Open_Clos='Closed' and PercentComp<>100, 1-(PercentCompletion/100), NULL))

count(if(CategoryDesc<>'Financial' and Open_Clos='Closed' and PercentComp<>100, 1-(PercentComp/100), NULL))

** Plus 9 others (not included) with similar configurations **

count(if(CategoryDesc='Financial' and Open_Clos='Closed' and PercentComp=100, sum(FinancialAttained), NULL))

count(isnull(PercentComp))

max(TOTAL <ID> DateOfUpdate)

You will see from the above that the expressions are done in pairings. I am trying to streamline the code to handle both at the same time, so the pariings should look more like this:

if (BenUpdateExists=1

,sum(if(BenefitCategoryDesc='Financial',1,0)
,sum(if(BenefitCategoryDesc='Financial', 1-(PercentCompletion/100), NULL))
)

**Bonus marks if you can see why this is returning only null results!!

I have removed all but one calculated dimension minor IF statement which does not appear to be causing any issues.

Thanks

Alan

Re: Fix memory issue - move calcs to script??

danielrozental — Fri, 07 Sep 2012 17:16:29 GMT

Calculated dimensions causes QlikView to stop using already generated associations between fields, that's causes a really poor performance. Avoid the IF as well, avoid any calculation if possible.

count(if(CategoryDesc='Financial' and Open_Clos='Closed', ID, NULL))

If you add a "1 AS FLG" in the same table as the ID field you could use an expression like this

sum({<CategoryDesc={'Financial'},Open_Clos={'Closed'}>}FLG)

That will probably outperform the previous one.

Re: Fix memory issue - move calcs to script??

robert99 — Sat, 08 Sep 2012 13:47:16 GMT

I use then (calculated dimensions) at times. They seem to work fine.

Re: Fix memory issue - move calcs to script??

Mon, 10 Sep 2012 14:10:20 GMT

Thanks Daniel.

How would I combine this with an IF statement?

Cheers

Alan