topic Re: date and time calculation in QlikView

date and time calculation

josephinetedesc — Sun, 19 Mar 2017 22:20:52 GMT

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...

Re: date and time calculation

sunny_talwar — Sun, 19 Mar 2017 22:36:46 GMT

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

TimeStamp#([Release Time], 'DD/MM/YYYY h:mm:ss TT') - TimeStamp#([Appt Time], 'D/M/YYYY h:mm:ss') as NewField

Re: date and time calculation

josephinetedesc — Sun, 19 Mar 2017 22:47:03 GMT

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

Re: date and time calculation

sunny_talwar — Sun, 19 Mar 2017 22:52:54 GMT

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

Re: date and time calculation

josephinetedesc — Mon, 20 Mar 2017 00:17:59 GMT

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!

Re: date and time calculation

sunny_talwar — Mon, 20 Mar 2017 01:12:11 GMT

Awesome, I am glad it all worked in the end