topic Re: Out of memory in QlikView

Out of memory

Wed, 10 Oct 2012 08:42:10 GMT

Can someone help me to understand what the following does in the script? After the last two steps it continues with 10 more steps but with diffrent QCode, The problem is that QV goes out of memory after 6 steps so in some way I have to rewright the script.

Tmp_MeritValue:

Load *,

if(RadNr = 12 and MeritValue> 0, ID) as Behor_12

;

Load *,

if(previous(ID) = ID, peek(RadNr)+1, 1) as RadNr

Resident Tmp_MV2

Order By ID, MeritValueDesc;

Drop Table Tmp_MV2;

MeritValue:

Load

DistinctID as ID_12

Resident Tmp_MeritValue

where isNull(Behor_12)=0;

Tmp_Behor_12:

Load

ID as ID_Behor,

MeritValue as MV_SV

Resident Tmp_MeritValue

where exists(ID_12, ID) and QCode= 'SV';

join
Load
ID as ID_Behor,
MeritValue as MV_SVA
Resident Tmp_MeritValue
where exists(ID_12, ID) and QCode= 'SV2';

Re: Out of memory

Miguel_Angel_Baeyens — Wed, 10 Oct 2012 08:56:04 GMT

Hi,

There is some script missing, the table Tmp_MeritValue loads from resident table Tmp_MV2. Which version are you running? In the script editor, when you Debug instead of normal reloading, what message does it show and where it does stop?

Regards,

Miguel

Re: Out of memory

Wed, 10 Oct 2012 09:48:54 GMT

Tmp_MV2 are ok, it´s beeing droped right after the second step from above in the previous post.

I only get a "Out of virtual and/or logical memory allocating 2 MB" message. Runs qv 10.

The script continue as follow

joinLoad
ID as ID_Behor,
MeritValue as MV_EN

Resident Tmp_MeritValue

where exists(ID_12, ID) and QCode= 'EN';

join

Load

ID as ID_Behor,
MeritValue as MV_MA

Resident Tmp_MeritValue

where exists(ID_12, ID) and QCode= 'MA';

join Load

ID as ID_Behor,
MeritValue as MV_FY

Resident Tmp_MeritValue

where exists(ID_12, ID) and QCode= 'FY';

....

Load

Same as above but diffrent QCode....After the 6th it goes out of memory.

So, Can you help me understand what the

" where isNull(Behor_12)=0; " means/does in the above post?

and what where exists(ID_12, ID) means/does? - I think this sorted out the fields where ID_12 are the same as ID and my guess is that there ID_Behor stacks up with several thousands per ID. Because if I only goes for say 3 join steps and then abort and click "show freqvens" in the ID_Behor field it already there have freqvens for each ID of app 6000.

Re: Out of memory

Wed, 10 Oct 2012 09:55:18 GMT

Sorry. My mistake. Have forget the Load DISTINCT....I think thats the case...Shall try and reload.

Re: Out of memory

Wed, 10 Oct 2012 09:59:31 GMT

Still currious about where isNull(Behor_12)=0;

IsNull = post that are null. Does it means that post thar are null are excluded in the load or what? Why the =0?

Re: Out of memory

Miguel_Angel_Baeyens — Wed, 10 Oct 2012 10:04:59 GMT

Hi,

In QlikView, any function that returns 0 equals to false, otherwise, it's true. The IsNull() value should return -1 (true) when the value is actually null, so "IsNull(Behor_12)=0" means when "Behor_12" has some data.

The LOAD DISTINCT does indeed need a lot of memory and CPU, and if the number of records is high, that could be the cause. The first test is comment part of the script and try reloading.

And yes, the Exists(Field2, Field1) is used to load those records where value in Field2 in the current table matches with any other value in Field1, previously loaded.

Hope that helps.

Miguel

Re: Out of memory

Wed, 10 Oct 2012 10:41:33 GMT

Hi. I got it to work now. Thanks for the help