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

[OmarBenSalem]

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 :

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..)

[hic]

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]

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]

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?