Load script calculations

[Anonymous]

Hi guys,

I wanted to ask you the suggestions about the most efficient way of making calculations in script task, like, arithmetic operations on different accounts.

For example, I have a table:

LOAD * INLINE [
Account, Property1, Amount
a1, k, 2
a1, k, 4
a1, m, 4
a1, m, 4
a2, k, 18
];

What I actually need is a new account a3 = a1 - a2. This expression means a3 = a1,k - a2,k = 6 - 18.

The formulas I have to deal with often look like this:

R300 = IIF(A400 = 0 or R401 is NULL,
0,
(Z500 * R501 + R401) / A400 + ValidatedAccountA
),
where all the R accounts are parents of some other calculated accounts, Z500 is a purely calculated formula and A400 is a simple input account. R300 is one of the calculated accounts used in the hierarchy.

What is the best way of recalculating these data? Using the calculations in the chart expression is not an option since I have a couple of hundreds of account to recalculate inside a hierarchy and its hierarchical complexity hinders the use of chart expressions, so I was thinking about doing it directly in the load script.

Moreover, the real data table contains a dozen of millions of records, so I would also like to make the script run fast.

Thanks in advance,

Valera

[spsrk_84 · Accepted solution]

Hi,

I feel do all the calculations at the script level itself if u do the at the chart level definitely it will result in OUT OF OBJECT MEMEORY EROR", and if u r using incremental load then it will not be a problem .

[Anonymous]

Hi ajay,

indeed, you are right. I actually had to make all the calculations on the script level. I had to create around 20 thousand lines of script code to load all the data... 😕

Thanks for the suggestion. 🙂

Valera