Dynamic Accumulation for different type

[rob_vander]

Hi All,

I came across weird requirement and I have no clue how to solve it.

I have below dataset. I want to have accumulation for each type but it is not full accumulation. I need to go 4 or 3 step back to perform accumulation which is defined in reference data for each Type. Another problem is I have 9-10 type for which there could be any AccumStep between 1 to 10. Hence, accumulation has to be dynamic. I will better explain it with example

// Actual data

Load * Inline [
Type, Sequence, Value
A, 1, 10
A, 2, 20
A, 3, 90
A, 4, 10
A, 5, 30

B, 1, 20
B, 2, 40
B, 3, 80
B, 4, 10

C, 2, 70
C, 3, 30
C, 4, 40

D, 1, 10
D, 2, 30
D, 3, 50
D, 4, 70 ];

// Reference data

Load * Inline [
Type, AccumStep
A, 4
B, 3
C, 2
D, 2 ];

let's say for Type =A and Sequence =5, I need to go back 4 row back because AccumStep in reference data is 4. Hence, Accum value will be,  (20+90+10+30 =150). For Type=A and Sequence =4, Accum will be (10+20+90+10=130). For Type=A and Sequence = 3, there is nothing 4 row back, so start with the first row , so Accum will be (10+20+90=120). For Type=A and Sequence=2, there is nothing 4 row back, so start with the first row , so Accum will be (10+20=30). For first row of any Type it will be actual value, so for Sequence=1, Accum will be 10. This is the example of Type A. Similar logic applies to all other Type considering it's AccumStep in reference data.

Output Reqired:

Type	Sequence	AccumStep	Value	Accum	logic
A	1	4	10	10	10
A	2	4	20	30	(10+20)
A	3	4	90	120	(10+20+90)
A	4	4	10	130	(10+20+90+10)
A	5	4	30	150	(20+90+10+30)
B	1	3	20	20	20
B	2	3	40	60	(20+40)
B	3	3	80	140	(20+40+80)
B	4	3	10	130	(40+80+10)
C	2	2	70	70	70
C	3	2	30	100	(70+30)
C	4	2	40	70	(30+40)
D	1	2	10	10	10
D	2	2	30	40	(10+30)
D	3	2	50	80	(30+50)
D	4	2	70	120	(50+70)

@Vegar @Kushal_Chawda @rwunderlich @MarcoWedel 

[rob_vander]

works well. thanks