How to calculate a time difference to the previous record and create a flag

[david145]

hi,

i have a table pulling in that gets all the rows for the max order per line. 

below is an example of the dataset when i filter it to just one line

ID	Date_Time_Unix_TimeStamp	Line	Order_Number	Date_Time
100	1661170026	Line1	5000	22-AUG-2022 13:07:06
100	1661170026	Line1	5000	22-AUG-2022 13:07:06
100	1661170026	Line1	5000	22-AUG-2022 13:07:06
172	1661170872	Line1	5000	22-AUG-2022 13:21:12
172	1661170872	Line1	5000	22-AUG-2022 13:21:12
172	1661170872	Line1	5000	22-AUG-2022 13:21:12
180	1661170872	Line1	5000	22-AUG-2022 13:51:12
185	1661170872	Line1	5000	22-AUG-2022 15:00:00
185	1661170872	Line1	5000	22-AUG-2022 15:00:00
185	1661170872	Line1	5000	22-AUG-2022 15:00:00
186	1661170872	Line1	5000	22-AUG-2022 15:10:00
191	1661180040	Line1	5000	22-AUG-2022 15:18:00

I now need to only pull in the data records for each "order_number" per line if they were saved to the db within 60 minutes of the previous record. (and always pull in the most recent record)

e.g: if i had 8 records for my order_number, the first 3 were all within 60 mins of the previous, but then there was a time of +60mins for the next 3 records. The final 2 records were within 60 mins of their previous. given the above scenario, i would only need to pull in the final 2 records, as there was records longer than 60mins before that so all previous can be ignored

Also to note, i always need to bring in the max record per line, even if that record is +60mins than previous record

how would i do this? would i need to create some sort of flag for this rule? how would the flag be smart enough to restart when a record was saved 60mins after a previous one?

thank you

[marcus_sommer]

For this you may use interrekord-functions to access previous records, maybe something like this:

t1:
load *, -(TimeDiff) > 1/24) as Flag;
load *,
   if(Key = previous(Key), DateTime - previous(DateTime), -1) as TimeDiff,
   if(Key = previous(Key), rangesum(peek('Counter'), 1), 1) as Counter
resident xyz order by Key, DateTime;

left join(t1) load Key, max(Counter) as Counter, 1 as CounterFlag
resident t1 group by Key;

Important for such an approach is to load the data resident with an appropriate ordering - Key is here just simplified for your order + lines + id ? or whatever. Within a following load you may then use the Flag fields within a where clause to exclude the unwanted ones.

- Marcus

[david145]

thanks for the reply @marcus_sommer 

just so i am understanding it correctly. would my key field be the "Date_Time" field? 

or would i need to create a unique ID for each record and then use that as my key?

the fields in my table are the only ones im working with in my dataset.

thanks,

David

[david145]

@marcus_sommer  i tried your example, 

this is the code i used:

Test:
Load *, Autonumber(Order_Number &'-'& Line &'-'& ID) as KEY

;
sql code;

test2:
load *,
if(KEY = previous(KEY), Date_Time - previous(Date_Time), -1) as TimeDiff,
if(KEY = previous(KEY), rangesum(peek('Counter'), 1), 1) as Counter
resident Test order by KEY, Date_Time;
drop table Test
;

test3:
load *,(TimeDiff) > .5/24 as Flag //changing it to 30mins
resident test2;
drop table test2;

however the flag is only coming up as zeros and im not sure why?

i have attached some data if it helps.

it shows 2 Lines that have all the records for the most recent order number.

not sure what im doing wrong here

[marcus_sommer]

In regard to the needed Key I'm not really sure which fields needs to be included and if a single one is sufficient to cover all needs/conditions. For the interrecord-functions you may not mandatory need one else it was just meant as a simplifying to avoid querying the previous records from multiple fields. For the group by it's a similar matter. Beside the simplicity should such a single Key perform better as doing it against multiple fields.

This means you may need to play a bit with the Key to see how the results change and which are right/wrong and why.

To make it short I suggest to remove the id from the Key and adding a recno() and a rowno() + a previous() from Key and DateTime (without any condition - just the values) to test2 and then you could see within the table which records come from where and are now loaded in which order and which results the previous() returned and if checking/calculation with them against the current values are valide or not. Usually makes such approach it quickly obvious what didn't work like expected.

- Marcus