Skip to main content

New to Qlik Sense

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
OmarBenSalem

Generic Load alternative - Error -129 (out of memory resources)

Hello, 

I'm doing a generic load that results in a -129 error (out of memory).. the question is, can I achieve my aim differently? @sunny_talwar  @hic @rwunderlich ..

Let me explain what I'm doing :

1) I have this table :

OmarBenSalem_0-1658414380138.png

 

This table contains a field RUB that have many values : example KCR10 and KCR20

For each RUB in this table, the Amount is calculated in the same way (sum of the something group by  sthing)

Until now, all is good..

 

2) the problem, there are many other RUB (that do not exist) but are rather calculated.

Example :

KCR00 = KCR010+KCR020 ..

So.. For me to be able to do this, I'd have to transform my table and create columns KCR010 and KCR020 rather than having them as values in one column

=> Generic load 

NAME MONTH KCR010 KCR020 KCR00 = KCR010+KCR020 ..
X 1 200 100 300
X 2 150 50 200

 

The script I've used :
[TMP1]:
GENERIC LOAD Name ,Month , RUB,Amount_YTD RESIDENT [Calculation];

 

[RESULT]:
LOAD Name ,Month RESIDENT [Calculation];
DROP TABLE [Calculation];

 

FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) AS Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'TMP1.*');
NEXT i

 

FOR i = 0 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
left join ([RESULT]) LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
NEXT i

 

DROP Table TableList;
NoConcatenate

 

Final_TEMP:
load Distinct *
, KCR10 + KCR20 as KCR00
Resident RESULT;
drop Table RESULT;

 

3) I then do a crosstable to add this new created RUB

FINAL:
CrossTable(RUB,Amount_YTD,2)
load Distinct * Resident Final_TEMP;
drop Table Final_TEMP;

 

This worked fine yesterday, but today it's always returing error -129 (out of memory resources) !!

The question is :

1) What am I doing wrong and how can I maybe change my script?

2) Can u think of another way to achieve this?

 

Thank u very much (it's very urgent..)

Labels (1)
3 Replies
hic
Employee
Employee

Multiple joins in the script will indeed eat a lot of memory...

I would solve it differently:

// === First load the data that you have:
Data:
LOAD Name, Month, RUB, Amount_YTD
RESIDENT [Calculation];

// === Then add the calculated RUB:s
Concatenate (Data)
LOAD Name, Month, 'KCR00' as RUB, Sum(Amount_YTD) as Amount_YTD
RESIDENT [Calculation]
Where Match(RUB,'KCR10','KCR20')
Group By Name, Month ;

If you have many additional RUB:s, you can create a For-Next loop to load them.

OmarBenSalem
Author

There are so many.. More than 50.. and it needs preceeding loads more than once..

example : 

KCR00= KCR010 + KCR020

KCR001 = KCR00*10 etc..

I'll try your solution, but I'd have 2 questions:

1) when it comes to generic load, am I doing it the "good way" or am I missing a step that could be less resource heavy?

2) I have 32g RAM and 16 cores.. but it still can't load the Application (Calculation table = 125k lines...). How many do I need to have for it to reload and more importantly how do I know/calculate this?

Thanks for your reactivity !

OmarBenSalem
Author

Another question , with your method, how do I do when it's 

KTR20 * BSPDF as BSPDN

or 

KUF90 - KUF03 AS KUF99

I can't do sum() ..

Any idea please?