cumulative sum of a pattern cycle

[michelbakker]

Hello, I want to make a cumulative sum of a range of numbers who forms a cyclegroup. Suppose i have a machine with tree robotic arms who putting things together. This arms forms a cycle of movements from arm 1 to 3.  So I want to make a cumulative sum of the duration from arm 1 to 3 and then do it all over again for the next cycle of movements. Below my 'near' solution. Have someone the perfect solution for me

The cycle start Always whit arm 1 but sometimes it will ends on arm 2.

T0:

LOAD * INLINE [

    ID, Cycle, Duration

    1, 1, 6

    2, 2, 23

    3, 3, 4

    4, 1, 3

    5, 2, 4

    6, 3, 5

    7, 1, 7

    8, 2, 3

    9, 3, 12

    10, 1, 4

    11, 2, 5

    12, 1, 6

    13, 2, 7

    14, 3, 2

];

NoConcatenate
T1:
load * ,
if((CycleStart) <> peek(CycleStart),  rangesum(Duration,peek('CumSum') ) ) as CumSum ;
Load
ID,
Cycle,
Duration,
if(Cycle <> 1,null(), Cycle) as CycleStart

Resident T0
order by ID ;

drop table T0 ;

[sunny_talwar · Accepted solution]

Try this

T0:

LOAD * INLINE [

    ID, Cycle, Duration

    1, 1, 6

    2, 2, 23

    3, 3, 4

    4, 1, 3

    5, 2, 4

    6, 3, 5

    7, 1, 7

    8, 2, 3

    9, 3, 12

    10, 1, 4

    11, 2, 5

    12, 1, 6

    13, 2, 7

    14, 3, 2

];

NoConcatenate

T1:

LOAD ID,

Cycle,

Duration,

If(Cycle <> 1, RangeSum(Duration, Peek('CumSum')), Duration) as CumSum

Resident T0

Order By ID;

DROP Table T0;

[michelbakker]

Thank You Sunny. Very helpfull !!!