Out of Object Virtual/Logical Memory - tips/tricks for increasing efficiency?

[sarahallen1]

I have a QVW that has some very complicated layout features (lots of if statements and set analysis within calculated dimensions, sort order expressions, labels, titles and expressions). 

One particular pivot table is giving me an "out of object memory" error, and sometimes also gives an "OUT OF VIRTUAL AND/OR LOGICAL MEMORY, allocating 0.03125 MB". 

If somebody could have a look at this table and suggest any tips or tricks for increasing the efficiency, I'd be really grateful.

The QVW is attached (script simplified here to come straight from QVDs, as it's just the layout I want to tweak).  The tables that are particularly struggling are those in the container box on the "Portfolio Analysis" sheet.  If you go to this sheet and select all metrics and all funds it falls over.

Thanks very much for your help!

Sarah

[#2]

Hi Sarah,

I have encountered the same error message recently, this error happen in both Table Box and Pivot Table.

For the issue on my Table Box, the work-around is just remove some of the un-use columns, then it can avoid this error.

But for my Pivot Table, which I need to display all 10 columns in the export Excel, I still have no idea how to resolve it.

[sarahallen1]

Thanks Jovis.  I've checked and I don't have any unused expressions unfortunately. 

I've got a weak workaround...  There are something like 250 possible values in one field so if none are selected the table gets really big and falls over with this error message.  So I have set an "on open sheet" trigger to automatically select certain values in this field, so that the table is limited.  Then the user has to change their field selections.  If they clear the field though (hence all 250 values are displayed again) it still falls over, so it's a bit poor for a workaround, but I can't find anything else.

Thanks for your help.

Sarah

[jonathandienst]

Sarah

Have you considered using conditional calculation for the pivot table? Simply set a condition statement in the Calculation Condition box to ensure that the user makes suitable selections. For example, lets assume that the user should make at least one selection from Field1 and 2 selections from Field2:

=GetSelectedCount(Field1) >= 1 And GetSelectedCount(Field2) >= 2

You can override the default 'Calculation condition unfilfilled' message by clicking on the Error Messages button and entering your own message.

Hope that helps

Jonathan

[#5]

I can't think of any solution other than to decrease the use of calculated dimensions. Perhaps make some of those in the script instead of the graph.

Calculated dimensions and if statements eat up a ton of memory. But when possible I would make any fields and use if statements in the back end script, not within your graphs.

ie I have a straight table in one of my QVW's thats displaying about 15 million rows in it with two dimensions and one expression. If I put in a calculated dimension is will run out of memory.

Hope this helps and good Qlikviewing!

[sarahallen1]

Thanks Jonathan.  It's a good idea and one that I hadn't considered.  However, I already have another calcuation condition on this object (the chart makes no sense if certain criteria are met), and the error message is tailored to explain that.  I could of course use an OR statement or something in the calc condition, and make the error message longer, but it wouldn't be very elegant.  Furthermore, I would actually quite like users to be able to see all 250 field values...

That said, it is a good idea and I will weigh up the pros and cons...  thanks!

Sarah

[sarahallen1]

Thanks Ethan.  I am trying to do as much as possible in the script but I'm dealing with really complicated expressions here (in both the dimensions and the expressions) and I think it would be a hugh amount of work (and very difficult) for me to try to get them in the script...

Thanks though!  If I feel particularly clever one day maybe I will try

Sarah

[jonathandienst]

Sarah

The custom error message is an expression, so you should be able to conditionally display the most important message for the error, depending on the current state.

Just a thought - have not tried this myself

Jonathan

[sarahallen1]

I didn't realise the error message was an expression - this will be really helpful!  Thanks!