Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Thanks Sunny, I'll give it try and revert
Hi Sunny, works a treat thanks!!
No problem at all