topic Dates/Times in QlikView

Dates/Times

jamelmessaoud — Wed, 25 Nov 2020 16:16:04 GMT

Hi guys,

I'm hoping someone can help

I have two dates which appear like this from the database 04/06/2018 16:00:00 and the two fields from the DB are Date Booked In and Arrival Date

I am trying to specify whether a delivery was On Time, Late or Early. So, basically if the Date Booked matches the Arrival Date then this is On Time etc. However here's the tricky bit! There is a 15 minute window allowed so if a Booked Date is 04/06/2018 10:30:00 and the Arrival Date is 04/06/2018 10:40:00 then this will be On Time the same applies if this was the other way round, so Booked Date 04/06/2018 10:40:00 and the Arrival Date 04/06/2018 10:30:00 this would also be On Time. The Late and the Early are self explanatory BUT only if outside of the 15 minute window!

Ideally I would like an IF statement within the load script but I cannot for the life of me figure this one out!?

Can anyone offer any advise?

Thanks

Re: Dates/Times

YoussefBelloum — Thu, 14 Jun 2018 12:01:34 GMT

EDITED

Hi,

try this:

if(Booked Date=Arrival Date or fabs(interval(Booked Date-Arrival Date,'mm'))<=15, 'ON TIME') as FIELD

if(Booked Date=Arrival Date or interval(Fabs(Booked Date-Arrival Date),'mm')<=15, 'ON TIME') as FIELD

Re: Dates/Times

jamelmessaoud — Mon, 18 Jun 2018 09:29:34 GMT

I figured this out in the end but thank you for the help Youssef

Re: Dates/Times

YoussefBelloum — Mon, 18 Jun 2018 11:55:14 GMT

you're welcome !