Cumulative load

[msantarone]

Given the following data source:

Material	Period	Quantity
1	201801	12
1	201801	-3
1	201802	-2
2	201801	18
2	201801	-3
2	201801	-5
2	201802	3

I need to obtain through a LOAD:

Material	Period	Quantity
1	201801	9
1	201802	7
2	201801	10
2	201802	13

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD * INLINE [

    Material, Period, Quantity

    1, 201801, 12

    1, 201801, -3

    1, 201802, -2

    2, 201801, 18

    2, 201801, -3

    2, 201801, -5

    2, 201802, 3

];

FinalTable:

NoConcatenate

LOAD Material,

Period,

If(Material = Previous(Material), RangeSum(Peek('Quantity'), Quantity), Quantity) as Quantity;

LOAD Material,

Period,

Sum(Quantity) as Quantity

Resident Table

Group By Material, Period

Order By Material, Period;

DROP Table Table;