topic Re: Question about getting minutes from timestamps using TIME(FRAC() in QlikView

Question about getting minutes from timestamps using TIME(FRAC()

Sat, 13 Dec 2014 17:27:03 GMT

Hello,

Thanks in advance for help!

I'm trying to write an expression where I'm checking a Scheduled start time (SCHED_START_TIME) vs. patient in the room time (PiR_DATE_TIME); if the latter is later, I would get the difference of the times in minutes, otherwise, it would be 0.

The expression I have below is not working - I get an error in calculation (have tried several variations to no luck)

if(time(frac(SCHED_START_TIME)) > time(frac(PiR_DATE_TIME)), time(frac(SCHED_START_TIME) - time(frac(PIR_DATE_TIME), 0)

(I would add an "AND" comparing end times as well).

The current "In Block" is good but the "Out Block" isn't always correct in the application so am trying to overwrite it.

THE Sched Start	THE Sched Stop	Pt In Rm	Pt Out Rm	Duration	Tot Sched Block	In Block	Out Block
						330	40
11/07/2014 08:00	11/07/2014 11:00	11/07/2014 08:00	11/07/2014 09:54	114	240	114	0
11/21/2014 08:00	11/21/2014 10:30	11/21/2014 07:57	11/21/2014 10:19	142	240	139	3
11/21/2014 10:30	11/21/2014 12:50	11/21/2014 10:43	11/21/2014 12:37	114	240	77	37

Re: Question about getting minutes from timestamps using TIME(FRAC()

MarcoWedel — Sat, 13 Dec 2014 18:28:05 GMT

RangeMax(PiR_DATE_TIME-SCHED_START_TIME,0)*1440

Re: Question about getting minutes from timestamps using TIME(FRAC()

rwunderlich — Sat, 13 Dec 2014 18:29:37 GMT

Like Marco said, but I avoid datetime arithmetic and use functions instead.

interval(RangeMax(0, PiR_DATE_TIME - SCHED_START_TIME),'m')

-Rob

Re: Question about getting minutes from timestamps using TIME(FRAC()

Sat, 13 Dec 2014 20:44:54 GMT

Many thanks Marco and Rob...

Could I trouble you about how I may combine this with one for POR_DATE_TIME and SCHED_STOP_TIME?

Re: Question about getting minutes from timestamps using TIME(FRAC()

MarcoWedel — Sat, 13 Dec 2014 20:48:35 GMT

Can you elaborate a bit more on your requirements for this combination?

thanks

regards

Marco

Re: Question about getting minutes from timestamps using TIME(FRAC()

Sat, 13 Dec 2014 20:52:18 GMT

Sorry I should have been more clear.

I want to check to see where there is time outside of the sched. vs. actual times so need to check both

PiR_DATE_TIME - SCHED_START_TIME

and

PoR_DATE_TIME - SCHED_STOP_TIME

So, if there is no time outside, there is a zero but if not, need to quantify sched vs. actual for start and stop.

Hope that clarifies.

Re: Question about getting minutes from timestamps using TIME(FRAC()

rwunderlich — Sat, 13 Dec 2014 21:10:30 GMT

I would think you could do:

interval(RangeMax(0, PiR_DATE_TIME - SCHED_START_TIME),'m')

interval(RangeMax(0, PoR_DATE_TIME - SCHED_STOP_TIME),'m')

-Rob

Re: Question about getting minutes from timestamps using TIME(FRAC()

Sat, 13 Dec 2014 21:16:31 GMT

I tried that and I only get "0"

Re: Question about getting minutes from timestamps using TIME(FRAC()

MarcoWedel — Sat, 13 Dec 2014 21:51:44 GMT

thanks, can you give some more exmaples of the output table you're looking for?

regards

Marco

Re: Question about getting minutes from timestamps using TIME(FRAC()

Sun, 14 Dec 2014 12:33:24 GMT

Sure Marco.

Please see below:

I'm comparing the Scheduled times to the actual (Pt in / out of room).

The current out of block calculation is not working correctly if you compare the times.

Pt In Rm	Pt Out Rm	THE Sched Start	THE Sched Stop	In Block	Out Block
11/17/2014 07:44	11/17/2014 11:23	11/17/2014 07:45	11/17/2014 10:45	219	0	should be out block =1
11/17/2014 11:49	11/17/2014 13:20	11/17/2014 11:00	11/17/2014 13:00	91	0	should be out block = 49+20=69

Re: Question about getting minutes from timestamps using TIME(FRAC()

rwunderlich — Mon, 15 Dec 2014 18:07:30 GMT

Are you sure all your fields are loaded as Timestamps? Put them in listboxes and change the Number format to Fixed to 4 places. Do they all look like decimal numbers eg 40234.02567?

-Rob

Re: Question about getting minutes from timestamps using TIME(FRAC()

wardzynski — Thu, 28 Sep 2017 01:15:58 GMT

Karen,

How did you get the duration in minutes, the difference between 2 timestamp in minutes?

Would be interesting to know!

Re: Question about getting minutes from timestamps using TIME(FRAC()

rwunderlich — Thu, 28 Sep 2017 14:47:35 GMT

One day = 1. You can multiply the difference by 1440 (the number of minutes per day) or my preferred method, use the Interval function.

Interval(myduration, 'm')

Interval(0.5, 'm') = 720

-Rob

Re: Question about getting minutes from timestamps using TIME(FRAC()

wardzynski — Thu, 28 Sep 2017 23:04:08 GMT

Thank you for that clear explanation, Rob!
I saw you also used the rangemax function, was that to be able to count null() values included in the interval range?

Re: Question about getting minutes from timestamps using TIME(FRAC()

rwunderlich — Fri, 29 Sep 2017 03:46:54 GMT

The RangeMax function was used in the original thread because the OP had some missing data and wanted to ensure the duration was never less than zero -- which would have been invalid in this case.

-Rob