cancel
Showing results for
Did you mean:
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:

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.

Labels (2)

• ### QlikWorld 2023

1 Solution

Accepted Solutions
MVP
try

SourceTable:
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
];

😧
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;
2 Replies
MVP
try

SourceTable:
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
];

😧
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;
Creator
Author

Thanks