If previous value greater than current value, dynamically replace with current value

lachlanwcast — Fri, 17 Feb 2023 03:48:08 GMT

So, similar to the other posts on the community around peek() and previous() when filling in data gaps, I am hoping to get some assistance:

Here is our table:

date_time	value
31/05/2022 9:30:01	43640168
31/05/2022 9:35:00	43640168
31/05/2022 9:35:02	43640168
31/05/2022 9:40:00	43640232
31/05/2022 9:40:03	43640232
31/05/2022 9:45:00	43640232
31/05/2022 9:45:04	43640232
31/05/2022 9:50:00	43600000
31/05/2022 9:50:05	43600000
31/05/2022 9:55:00	43600000
31/05/2022 9:55:03	43600000
31/05/2022 10:00:00	43640360
31/05/2022 10:00:05	43640360
31/05/2022 10:05:00	43640360
31/05/2022 10:05:07	43640360
31/05/2022 10:10:00	43640432
31/05/2022 10:10:08	43640432
31/05/2022 10:15:00	43640480
31/05/2022 10:15:11	43640480
31/05/2022 10:20:00	43640480
31/05/2022 10:20:12	43640480
31/05/2022 10:25:00	43640552
31/05/2022 10:25:05	43640552

What we need the table to look like (required value column)

date_time	value	required value
31/05/2022 9:30:01	43640168	43640168
31/05/2022 9:35:00	43640168	43640168
31/05/2022 9:35:02	43640168	43640168
31/05/2022 9:40:00	43640232	43640232
31/05/2022 9:40:03	43640232	43640232
31/05/2022 9:45:00	43640232	43640232
31/05/2022 9:45:04	43640232	43640232
31/05/2022 9:50:00	43600000	43640232
31/05/2022 9:50:05	43600000	43640232
31/05/2022 9:55:00	43600000	43640232
31/05/2022 9:55:03	43600000	43640232
31/05/2022 10:00:00	43640360	43640360
31/05/2022 10:00:05	43640360	43640360
31/05/2022 10:05:00	43640360	43640360
31/05/2022 10:05:07	43640360	43640360
31/05/2022 10:10:00	43640432	43640360
31/05/2022 10:10:08	43640432	43640360
31/05/2022 10:15:00	43640480	43640360
31/05/2022 10:15:11	43640480	43640360
31/05/2022 10:20:00	43640480	43640360
31/05/2022 10:20:12	43640480	43640360
31/05/2022 10:25:00	43640552	43640360
31/05/2022 10:25:05	43640552	43640360

We are trying to fill in (dynamically, i.e. a varying number of rows) where the current row is less than the previous row, use the last number than isn't lower

Our simple script (below) works for the first instance (highlighted in green), but no instances after this. I assume because it's checking the previous value which is in a new field now

LOAD date_time, if(value<previous(value),peek('value_calc'),value) as value_calc resident table ORDER by date_time asc;

The 'required' value column in the table above is the type of thing we are looking for, i.e. when 43600000 is detected, as it's lower than the previous value, it should be replaced with the previous value. We just need this to occur in every row, until the 'next' loaded value is higher as it goes down the table, sorted by the timestamp

Any assistance would be appreciated...

Re: If previous value greater than current value, dynamically replace with current value

brunobertels — Fri, 17 Feb 2023 08:47:10 GMT

Seems to work fine form me :

[Table]:
LOAD * INLINE
[
date_time,value,required value
31/05/2022 09:30,43640168,43640168
31/05/2022 09:35,43640168,43640168
31/05/2022 09:35,43640168,43640168
31/05/2022 09:40,43640232,43640232
31/05/2022 09:40,43640232,43640232
31/05/2022 09:45,43640232,43640232
31/05/2022 09:45,43640232,43640232
31/05/2022 09:50,43600000,43640232
31/05/2022 09:50,43600000,43640232
31/05/2022 09:55,43600000,43640232
31/05/2022 09:55,43600000,43640232
31/05/2022 10:00,43640360,43640360
31/05/2022 10:00,43640360,43640360
31/05/2022 10:05,43640360,43640360
31/05/2022 10:05,43640360,43640360
31/05/2022 10:10,43640432,43640360
31/05/2022 10:10,43640432,43640360
31/05/2022 10:15,43640480,43640360
31/05/2022 10:15,43640480,43640360
31/05/2022 10:20,43640480,43640360
31/05/2022 10:20,43640480,43640360
31/05/2022 10:25,43640552,43640360
31/05/2022 10:25,43640552,43640360
](delimiter is ',') ;

NoConcatenate
final:
load *,
if(value<peek('NewValue'),peek('NewValue'),value) as NewValue;
load *

resident Table order by date_time asc;
drop table Table;

topic Re: If previous value greater than current value, dynamically replace with current value in App Development

If previous value greater than current value, dynamically replace with current value

Re: If previous value greater than current value, dynamically replace with current value