Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
jamelmessaoud
Creator II
Creator II

Dates/Times

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

J

3 Replies
YoussefBelloum
Champion
Champion

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

jamelmessaoud
Creator II
Creator II
Author

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

YoussefBelloum
Champion
Champion

you're welcome !