Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
adimiz123
Creator
Creator

Trying to complete data from previous records, trying to skip low values.

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.

Labels (2)
1 Solution

Accepted Solutions
maxgro
MVP
MVP

try
 
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
2111,1,100
2111,2,2
2111,3,2
2111,4,1
2111,5,2
2111,6,2
2111,7,70
2111,8,150
2111,9,200
2111,10,230
];
 
😧
NoConcatenate LOAD
Car,
RowNum,
//KM as KM_Orig,
If(peek(Car)=Car and KM<=peek(KM), Peek(KM), KM) as KM
Resident
SourceTable
Order by
Car,
RowNum;
 
DROP Table  SourceTable;

View solution in original post

2 Replies
maxgro
MVP
MVP

try
 
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
2111,1,100
2111,2,2
2111,3,2
2111,4,1
2111,5,2
2111,6,2
2111,7,70
2111,8,150
2111,9,200
2111,10,230
];
 
😧
NoConcatenate LOAD
Car,
RowNum,
//KM as KM_Orig,
If(peek(Car)=Car and KM<=peek(KM), Peek(KM), KM) as KM
Resident
SourceTable
Order by
Car,
RowNum;
 
DROP Table  SourceTable;
adimiz123
Creator
Creator
Author

Thanks