Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Fix memory issue - move calcs to script??

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

5 Replies
Master II
Master II

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

Post your expressions/calculated dimensions.

Not applicable

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

Master II
Master II

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.

Specialist III
Specialist III

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

Not applicable

Thanks Daniel.

How would I combine this with an IF statement?

Cheers

Alan