Check each row value between flags and update the Value field

[quser0098]

Hi devs, 

Trying to update the Value column with the highest value for the product by looking into the rows between the flag 1 and -1 in the edit script. I managed to get the output in the front end using above and below functions but I would like to do this in the edit script.

Sample data laid out below and the expected result. 

rowno	Product	flag	Value		Result
1	101	1	1		4
2	101	1	4		4
3	101	-1	8
4	101	1	10		10
5	101	1	2		10
6	101	1	3		10
7	101	-1	1
8	101	1	9		15
9	101	1	6		15
10	101	1	10		15
11	101	1	15		15

[Saravanan_Desingh]

The Result 1 is not 8?

[quser0098]

Well, it should ignore -1 value but the range to compare should be from the first row until the row where the flag value is -1.

[Saravanan_Desingh]

Check this.

tab1:
LOAD *, If(Previous(flag)=-1 Or IsNull(Peek(K1)), RangeSum(Peek(K1),1), Peek(K1)) As K1
;
LOAD * INLINE [
    rowno, Product, flag, Value
    1, 101, 1, 1
    2, 101, 1, 4
    3, 101, -1, 8
    4, 101, 1, 10
    5, 101, 1, 2
    6, 101, 1, 3
    7, 101, -1, 1
    8, 101, 1, 9
    9, 101, 1, 6
    10, 101, 1, 10
    11, 101, 1, 15
    12, 101, -1, 9
];

Left Join(tab1)
LOAD K1, Max(Value) As Result
Resident tab1
Group By K1;

[Saravanan_Desingh · Accepted solution]

With correction.

tab1:
LOAD *, If(Previous(flag)=-1 Or IsNull(Peek(K1)), RangeSum(Peek(K1),1), Peek(K1)) As K1
;
LOAD * INLINE [
    rowno, Product, flag, Value
    1, 101, 1, 1
    2, 101, 1, 4
    3, 101, -1, 8
    4, 101, 1, 10
    5, 101, 1, 2
    6, 101, 1, 3
    7, 101, -1, 1
    8, 101, 1, 9
    9, 101, 1, 6
    10, 101, 1, 10
    11, 101, 1, 15
    12, 101, -1, 9
];

Left Join(tab1)
LOAD K1, Max(Value) As Result
Resident tab1
Where flag<>-1
Group By K1;