Accumulate values until next occurrence

[stuartbeet]

Invoice No	Invoice Line	MTO_Key	%Cus_Ref	%Prod_Code	Value	MTO
10808677	10808677*1	10808677-1	CHO006	31656	0.00	1
10808677	10808677*2	10808677-0	CHO006	EXOG-CURL-15229	61.52	0
10808677	10808677*3	10808677-0	CHO006	EXOG-CUSH-PIP43	5.58	0
10808677	10808677*4	10808677-1	CHO006	31844	0.00	1
10808677	10808677*5	10808677-0	CHO006	EXOC-CUSH-PIP43	4.31	0
10808677	10808677*6	10808677-0	CHO006	EXOC-TIEB-PIP	7.52	0
10808677	10808677*7	10808677-0	CHO006	SPEC-CARRIAGE	8.00	0

Hi, in the table above I need to accumulate the value in field Value until the the next occurrence of MTO and then apply the accumulated value to the lines where MTO = 1. So the end result should see line 1 = 67.10 (line1 + line2 + line3), and line 4 as 19.83 (line4 + line5 + line6 + line7). I have tried various attempts using previous and peek without success. Any suggestions would be most welcome.

[sunny_talwar]

Try this

Table:

LOAD * INLINE [

    Invoice No, Invoice Line, MTO_Key, %Cus_Ref, %Prod_Code, Value, MTO

    10808677, 10808677*1, 10808677-1, CHO006, 31656, 0.00, 1

    10808677, 10808677*2, 10808677-0, CHO006, EXOG-CURL-15229, 61.52, 0

    10808677, 10808677*3, 10808677-0, CHO006, EXOG-CUSH-PIP43, 5.58, 0

    10808677, 10808677*4, 10808677-1, CHO006, 31844, 0.00, 1

    10808677, 10808677*5, 10808677-0, CHO006, EXOC-CUSH-PIP43, 4.31, 0

    10808677, 10808677*6, 10808677-0, CHO006, EXOC-TIEB-PIP, 7.52, 0

    10808677, 10808677*7, 10808677-0, CHO006, SPEC-CARRIAGE, 8.00, 0

];

FinalTable:

LOAD *,

If([Invoice No] = Previous([Invoice No]),

If(Previous(MTO) = 1, Value, RangeSum(Peek('NewValue'), Value)), Value) as NewValue

Resident Table

Order By [Invoice No], [Invoice Line] desc;

DROP Table Table;

[stuartbeet]

Thanks Sunny, I'll give it try and revert

[stuartbeet]

Hi Sunny, works a treat thanks!!

[sunny_talwar]

No problem at all