Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have 3 columns:
Appt Date retrieved thus via SQL: 1/12/2016 12:00:00
Scheduled Appt Time; retrieved thus via SQL: 1/01/1900 8:30:00 AM
Release Time: retrieved thus via SQL: 30/11/2016 2:36:00 PM
I want to simply subtract the Appt Time from the Release Time but I am stumped how to do this.
It took me a while to even see what the Appt Time was doing...
Jo
Are these in the same table or is it possible to bring them in the same table using unique key or unique set of keys? If they are then you just need to subtract them I guess (if they are read as date) or help QlikView understand that these are date and then subtract them
[Release Time] - [Appt Time] as NewField
or
TimeStamp#([Release Time], 'DD/MM/YYYY h:mm:ss TT') - TimeStamp#([Appt Time], 'D/M/YYYY h:mm:ss') as NewField
Hi Sunny
yes they are in the same table but in the above example:
Release time = 30/11/2016 2:36 pm but the Appt Time = 1/01/1900 8:30 am
I need to somehow get rid of the 1/01/1900 component and replace it with the 1/12/2016 from the ApptDate
??
Jo
May be floor Appt Date and add Frac Appt Time to it....
TimeStamp#([Release Time], 'DD/MM/YYYY h:mm:ss TT') -
(Floor(TimeStamp#([Appt Date], 'D/M/YYYY h:mm:ss')) +
Frac(TimeStamp#([Scheduled Appt Time], 'D/M/YYYY h:mm:ss TT'))) as NewField
Thank you!
this ended up working ...
date((Floor(AppointmentDate) +Frac(ScheduledAppointmentTime)),'dd/MM/yyyy hh:mm' )
then the complete formula
interval(ReleasedTime - date(Floor(AppointmentDate) +Frac(ScheduledAppointmentTime)), 'mm')
Thanks Again!
Jo
Awesome, I am glad it all worked in the end