"Out of object memory" problem and ONLY() function

[#1]

Hi all,

I attached the .qvw that I will be talking about.

I am getting an "Out of object memory" error when running a particular chart. I am afraid that the reason behind this problem is the large amount of 'if' statements that I am using in both the chart dimensions and also in the expressions. I am using these values since I am working with a Bill of Material Hierarchy with a depth of 7. One example of this hierarchy is the following:

Thus the reasoning is the following:

If level 7 is not null, consider value associated with level 7, else (where value includes, Dep., Op., Res., Pcs/Hr etc.)

If level 6 is not null, consider value associated with level 6, else

If level 5 is not null, consider value associated with level 5, else

Etc,

Etc.

This process is done to find the root of the hierarchy and thus consider its value. This chart needs to display around 300 hierarchies!! Do you think that the complexity of this process is causing the "out of object memory" error? I is worth mentioning that when I only select just one hierarchy (ie. level 1) I am not getting this error.

After several attempts I found that if I enclose the expressions in an ONLY function, all works fine! To be honest I don't know why I am not getting the error when using ONLY. But, now I need to use SUM within the ONLY function as shown below:

=ONLY(

(Volumes/num(

if (NOT isNULL(Material7ScheduleStandardRunTime),  sum(Material7ScheduleStandardRunTime),

if (NOT isNULL(Material6ScheduleStandardRunTime),  sum(Material7ScheduleStandardRunTime),

if (NOT isNULL(Material5ScheduleStandardRunTime),  sum(Material7ScheduleStandardRunTime),

if (NOT isNULL(Material4ScheduleStandardRunTime),  sum(Material7ScheduleStandardRunTime),

if (NOT isNULL(Material3ScheduleStandardRunTime),  sum(Material7ScheduleStandardRunTime),

if (NOT isNULL(Material2ScheduleStandardRunTime),  sum(Material7ScheduleStandardRunTime),

if (NOT isNULL(Material1ScheduleStandardRunTime),  sum(Material7ScheduleStandardRunTime),

null())))))))

,'0.0'))

)

However this is returning blank expression values:

Can someone help me with the problem, preferably without changing the main structure of the approach that I am using? Any help would be appreciated since I've been trying to find a way around this problem for quite a long time!

Please find the .qvw attached. !

Thanks,
Matt

[Gysbert_Wassenaar]

The problem is you are nesting aggregations functions.

=ONLY(

(Volumes/num(

if (NOT isNULL(Material7ScheduleStandardRunTime),  sum(Material7ScheduleStandardRunTime),

Yes, only() is also an aggregation function. You need to use the Aggr function to specify over which dimensions the nested aggregation should be calculated.

ONLY(

(Volumes/num(

if (NOT isNULL(Material7ScheduleStandardRunTime),  Aggr(sum(Material7ScheduleStandardRunTime), Level1, Level2 ...etc ),

I'm not sure what you're trying to calculate. If it's about something like a BOM then perhaps you should remodel your document using the Hierarchy function. This blog post might be of interest: Bill of Materials‌.

[#3]

Hi Gysbert,

Thanks for your replay! I tried the aggr() function but unfortunately I am still getting blank values for the "Hrs" dimension.

The blog you posted look brilliant, but it requires a complete overhaul of what I already have. I will surely consider this model but at the moment I just need this to work at the moment.

Is there a way how to avoid getting the "out of memory object" error without using the only() function?

Thanks again!

[Gysbert_Wassenaar]

Sure, add more ram to the machine. Or load less data into the document.

[#5]

So what effect does only() have on the memory? I am quite new to Qlikview so I am trying to understand the difference in performance between using only() and without using only.

[Gysbert_Wassenaar]

Only() isn't the problem. If you remove it from the expressions in your top pivot table the result is the same. That's because you have in fact changed nothing. If you don't explicitly add an aggregation function then Qlikview will use Only() implicitly.

[#7]

Yes I agree that only() won't make any difference in the results and that Qlikview use it implicitly when no aggregation function is used. However, if I don't use the only() function (and thus no aggregation functions at all), I am getting the "out of object memory" error. If on the other hand, I use the only() function (as the only aggregation function) I am not getting the error. This is quite strange considering that Qlikview use only() implicitly if no other aggregation functions are used.