Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
david145
Contributor II
Contributor II

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

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

Labels (3)
4 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

@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
MVP & Luminary
MVP & Luminary

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