Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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

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 StartTHE Sched StopPt In RmPt Out RmDurationTot Sched BlockIn BlockOut Block
33040
11/07/2014 08:0011/07/2014 11:0011/07/2014 08:0011/07/2014 09:541142401140
11/21/2014 08:0011/21/2014 10:3011/21/2014 07:5711/21/2014 10:191422401393
11/21/2014 10:3011/21/2014 12:5011/21/2014 10:4311/21/2014 12:371142407737
14 Replies

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

  RangeMax(PiR_DATE_TIME-SCHED_START_TIME,0)*1440

MVP & Luminary
MVP & Luminary

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

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

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

-Rob

Not applicable

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

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()

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

thanks

regards

Marco

Not applicable

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

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.

MVP & Luminary
MVP & Luminary

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

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

Not applicable

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

I tried that and I only get "0"

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

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

regards

Marco

Not applicable

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

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 RmPt Out RmTHE Sched StartTHE Sched StopIn BlockOut Block
11/17/2014 07:4411/17/2014 11:2311/17/2014 07:4511/17/2014 10:452190should be out block =1
11/17/2014 11:4911/17/2014 13:2011/17/2014 11:0011/17/2014 13:00910should be out block = 49+20=69