topic Re: IF logic statement causing memory issues in QlikView

IF logic statement causing memory issues

Fri, 31 May 2013 11:14:16 GMT

Hi,

I have a Straight Table and I want to restrict the data displayed based on the following logic within a Calculated Dimension (so that I can suppress when value is null):

=IF(OnTrackingSheet='Yes' OR Over500k?='Yes', ID,

IF(Progress<>'Closed' AND RAG='Red' AND Active='Active', ID,

NULL()))

This causes the error message box:

"Out of virtual and / or logical memory allocating 2MB"

The Straight Table then breaks displaying error:

"Allocated memory exceeded"

My question is how do I retain this logic in a more efficient manner that doesn't kill the object?

The data required for the above IF statement is spread across four tables. Based on a process of elimination the Over500k?='Yes' component seems to be the trouble-maker. Once Over500k?='Yes' is removed everything works ok. This, needless to say, is not acceptable from a user perspective.

As they are OR statements I cannot use selection triggers.

I am open to creating this logic in the script at load time however I have never linked up multiple tables like this before....is this even feasible?

Any help greatly appreciated.

Thanks!

Re: IF logic statement causing memory issues

Gysbert_Wassenaar — Fri, 31 May 2013 11:19:28 GMT

Try [Over500k?] instead of Over500k?. If that doesn't work try renaming the field in the script to for example IsOver500k.

Re: IF logic statement causing memory issues

Fri, 31 May 2013 11:32:07 GMT

Thanks for the suggestion Gysbert.

I have renamed to Over500k and [Over500k] however the issue remains.

Re: IF logic statement causing memory issues

Gysbert_Wassenaar — Fri, 31 May 2013 11:49:17 GMT

Then the calculated dimension is probably just too complicated to calculate for your data set. Maybe you can change your expressions over to set analysis expressions:

sum({<OnTrackingSheet={'Yes'},Over500k={'Yes'},Progress-={'Closed'},RAG={'Red'},Active={'Active'}>} MyValue)

The expression editor will flag -= as wrong syntax, but that's a bug in the expression editor.

Also, I'd replace 'Yes' and 'No' values in the script with dual values so they have a numeric value as well. Calculations on numeric values are faster than string comparisons. See this blog post for a quick fix.

Re: IF logic statement causing memory issues

er_mohit — Fri, 31 May 2013 12:00:08 GMT

Try this

Aggr(Only({$<OnTrackingSheet={'Yes'}+Over500k?={'Yes'},Progress-={'Closed'}>}*RAG={'Red'}*Active={'Active'} ID), ID)