Replace value in a row by previous numbers dynamically.

[#1]

Dear QV support,

I have received a data set with altitudes.  It contains a simple list but the altitudes are not always correct, due to the instrument defect. Every row in a table is a time point of 1 second.

See here the simplified list

ID   Altitude       Altitude-corrected

1   -1000            0

2   -1000            0

3   -1000            0

4          0            0

5          2            2

6          3            3

7          5            5

8          8            8

9       100           8

10     100           8

11         9           9

12       10         10

13       13         13

14       15         15

• Altitude < -1 m is impossible, it must be resetted to zero.
• Altitude with > 10 meter difference with previous altitude is impossible, it need to be brought back to previous value.

See the table column “Altitude-correected”.

I have already written a script. It works fine for only one altitude score of 100 in a table but not two.

Could you please have a look at it?

I suppose this should be solved by a loop because it sometimes happens that number of ‘100’ is more than 3, 5, 9 and even 12.

Best regards,

Cornelis

[sunny_talwar · Accepted solution]

May be this:

Table1:

LOAD RowNo() as ID, 

    altitude, 

    If(altitude < -1, 0, altitude) as height

Inline [

altitude

-1000

-1000

-1000

0

2

3

5

8

100

100

9

10

13

15

];

Table2:

LOAD *,

    If(height - Peek('New_Height') > 10, Peek('New_Height'), height) as New_Height   

Resident Table1;

Drop Table Table1;

[#3]

Dear Sunny T,

That is very quick

This script provides the answer:

If(height - Peek('New_Height') > 10, Peek('New_Height'), height) as New_Height 

Really useful!

Have  a great day!

Cornelis