date and time calculation

[josephinetedesc]

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

[sunny_talwar]

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

[josephinetedesc]

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

[sunny_talwar]

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

[josephinetedesc]

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

[sunny_talwar]

Awesome, I am glad it all worked in the end