3 Replies Latest reply: Dec 12, 2016 1:46 AM by Maciej Kaniewski

# Calculations on rows in Load script

Hi all,

I recently had a quite difficult calculation issue which you may find interesting.

What is more - I'm not proud of my final script so maybe you can provide a better solution.

My source data looks like this:

RowNrProductIDDataFromDataToValue
1104.01.201609.01.201614
2105.01.201610.01.201654
3108.01.201616.01.201636
4110.01.201618.01.201675
5115.01.201618.01.201639
6116.01.201620.01.201676
7118.01.201624.01.201634
8119.01.201626.01.201665
9120.01.201626.01.201654
10124.01.201628.01.201666

All I need to do is that for all records I have to calculate Sum of all previous records which meet the condition:

DateTo from previous records has to be between DateFrom and DateTo for calculated row,

that means, for row with RowNr = 6 I am looking for all rows with DateTo between 16.01.2016 and 20.01.2016,

so my calculated value will be: 226 (36+75+39+76)

In that way I'll get:

RowNrProductIDDateFromDateToValue

SumOfValue

1104.01.201609.01.20161414
2105.01.201610.01.20165468
3108.01.201616.01.201636104
4110.01.201618.01.201675165
5115.01.201618.01.201639

190

6116.01.201620.01.201676226
7118.01.201624.01.201634224
8119.01.201626.01.201665175
9120.01.201626.01.201654229
10124.01.201628.01.201666219

My script looks like this:

if(ProductID=peek(ProductID, -1) and peek(DateTo, -1) <= DataTo and peek(DateTo, -1) >= DateFrom, peek(Value, -1) +

if(ProductID=peek(ProductID, -2) and peek(DateTo, -2) <= DataTo and peek(DateTo, -2) >= DateFrom, peek(Value, -2) +

if(ProductID=peek(ProductID, -3) and peek(DateTo, -3) <= DataTo and peek(DateTo, -3) >= DateFrom, peek(Value, -3) +

if(ProductID=peek(ProductID, -4) and peek(DateTo, -4) <= DataTo and peek(DateTo, -4) >= DateFrom, peek(Value, -4) +

if(ProductID=peek(ProductID, -5) and peek(DateTo, -5) <= DataTo and peek(DateTo, -5) >= DateFrom, peek(Value, -5) +

if(ProductID=peek(ProductID, -6) and peek(DateTo, -6) <= DataTo and peek(DateTo, -6) >= DateFrom, peek(Value, -6) +

if(ProductID=peek(ProductID, -7) and peek(DateTo, -7) <= DataTo and peek(DateTo, -7) >= DateFrom, peek(Value, -7) +

+ Value as SumOfValue

From ...

Data is ordered by ProductID and DateTo.

Let's assume that in data source there will be no more than 7 rows to check.

I've got milions of rows because of many ProductID.

Calculations have to be done in script. I can't do it with Set Analysis in chart.

Now I am looking for better solution.

Peek function works pretty fast but I believe it's not the best soluton and I know script masters can do it better.

I also predict that I will have to calculate more than only 7 rows in the future, so my script is not scalable.

I tried loops, joins, interval match but all of them are too heavy and can't calculate milions of loops for each row in real time.

Any advices with script functions will be appreciated!

Thank you.

• ###### Re: Calculations on rows in Load script

Perhaps an intervalmatch works. Though that will add its own overhead. I have no idea how it works with millions of records (even if you need just three or four fields). See attached example. You could join the resulting tables in the script and calculate the sums there if you want.

• ###### Re: Calculations on rows in Load script

Thank you Gysbert for your app.

This sample with IntervalMatch calculates my values exactly in the way how I would like it to be but it is much slower than calculations with peek function.

Let me make some more tests.

Maybe in the mean time somebody would share some other ideas.

• ###### Re: Calculations on rows in Load script

Thanks again Gysbert for your example.