Hi
I am trying to reach the 'Destination Table' below from this 'SourceTable' data structure.
I try to use -
If(peek(Car)=Car and KM<=peek(KM), Peek(KM),KM) as KM
but it not gives the desire solution.
SourceTable:
LOAD * Inline [
Car, RowNum, KM
111,1,100
111,2,2
111,3,2
111,4,1
111,5,2
111,6,2
111,7,70
111,8,150
111,9,200
111,10,230
];
DestinationTable:
Car | RowNum | KM
---------------------
111 | 1 | 100
111 | 2 | 100
111 | 3 | 100
111 | 4 | 100
111 | 5 | 100
111 | 6 | 100
111 | 7 | 100
111 | 8 | 150
111 | 9 | 200
111 | 10 | 230
The rule is that a row is more advanced, it cannot have a KM value lower than its previous rows, in case the previous KM values are low, put in these rows the KM that appears in the current row.
I try to avoid loops because of the amount of data.
Appreciate your help.
