Calculations on rows in Load script

[#1]

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:

RowNr	ProductID	DataFrom	DataTo	Value
1	1	04.01.2016	09.01.2016	14
2	1	05.01.2016	10.01.2016	54
3	1	08.01.2016	16.01.2016	36
4	1	10.01.2016	18.01.2016	75
5	1	15.01.2016	18.01.2016	39
6	1	16.01.2016	20.01.2016	76
7	1	18.01.2016	24.01.2016	34
8	1	19.01.2016	26.01.2016	65
9	1	20.01.2016	26.01.2016	54
10	1	24.01.2016	28.01.2016	66

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:

RowNr	ProductID	DateFrom	DateTo	Value	SumOfValue
1	1	04.01.2016	09.01.2016	14	14
2	1	05.01.2016	10.01.2016	54	68
3	1	08.01.2016	16.01.2016	36	104
4	1	10.01.2016	18.01.2016	75	165
5	1	15.01.2016	18.01.2016	39	190
6	1	16.01.2016	20.01.2016	76	226
7	1	18.01.2016	24.01.2016	34	224
8	1	19.01.2016	26.01.2016	65	175
9	1	20.01.2016	26.01.2016	54	229
10	1	24.01.2016	28.01.2016	66	219

My script looks like this:

Load RowNr, DateFrom, DateTo, Value,

    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.

[Gysbert_Wassenaar · Accepted solution]

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.

[#3]

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.

[#4]

Thanks again Gysbert for your example.

[PRO_11]

@Gysbert_Wassenaar ,

I am looking for same  question to sum trailing 30 days for given date field in same table. Would you be  share the IntervalMatch script here? i am unable to open QVW file in qliksense. 

[PRO_11]

@Gysbert_Wassenaar 

Here sample data and Question. Appreciated  if can get help on looping the date .

https://community.qlik.com/t5/New-to-Qlik-Sense/QlikSense-Trailing-30-days-sum-for-given-date-in-tab...