Need help - minimum date/time for a unique provider and room - urgent!

[#1]

Any help you may provide is appreciated and I'm new to Qlik syntax!

I have to determine "first case starts" in an operating room.

First case is counted as the minimum date/time for a given provider and the first room they occupy (sometimes they go between two rooms in a case).

Also, first case times are between 7 a.m. and 8:30 a.m. with a 5 minute grace period on each side.

PIR_TIME = patient in room time

I have the following in my load script to flag any of the cases between 7 a.m. and 8:30 a.m. with a 5 minute grace period:

if(num(Time(PIR_TIME,'hh:mm:ss')) > num(time('6:54:59','hh:mm:ss')) and num(time(PIR_TIME,'hh:mm:ss')) <= num(time('8:35:00','hh:mm:ss')) , 'Y', 'N' ) as TFInWindow, //5 mins grace

But I'm struggling with how to add in counting a DISTINCT provider and a DISTINCT first room.

So, here's a hypothetical example:

Date	Room	SURGEON	Sched Start	PIR
11/21/2014	1	Dr B	08:00	11/21/2014 08:00
11/21/2014	2	Dr B	08:00	11/21/2014 07:50
11/21/2014	3	Dr B	08:00	11/21/2014 07:00
11/21/2014	4	Dr C	08:00	11/21/2014 07:02
11/21/2014	5	Dr C	08:00	11/21/2014 07:56

Dr. B's first case is Room 3 at 7; however, he's going among 3 rooms within the time period but I don't want to count all of these just the Room 3 one.

Likewise Dr. C's first case is in Rm 4; however he's going between 2 rooms within the time period.

Again, appreciate your help!

[Anonymous]

How about something like this ?

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm';

Temp:

LOAD * INLINE [

    Date, Room, SURGEON, Sched Start, PIR

    11/21/2014, 1, Dr B, 08:00, 11/21/2014 08:00

    11/21/2014, 2, Dr B, 08:00, 11/21/2014 07:50

    11/21/2014, 3, Dr B, 08:00, 11/21/2014 07:00

    11/21/2014, 4, Dr C, 08:00, 11/21/2014 07:02

    11/21/2014, 5, Dr C, 08:00, 11/21/2014 07:56

];

Data :

load

SURGEON,

timestamp(min(timestamp(PIR))) as PIR

resident Temp

group by SURGEON

;

left join (Data)

load

SURGEON,

Room ,

PIR

resident Temp

;

Drop table Temp ;

[#3]

Hi Bill,

Finally wrapped my brain around the script you suggested and now I understand it is showing the first case only for Dr. B and Dr. C.

This is great!

I'd like to show all of Dr. B and Dr. C cases having the criteria

if(num(Time(PIR_TIME,'hh:mm:ss')) > num(time('6:54:59','hh:mm:ss')) and num(time(PIR_TIME,'hh:mm:ss')) <= num(time('8:35:00','hh:mm:ss')) , 1, 0) as TFInWindow, //5 mins grace

So for Dr. B output,

Date, Room, SURGEON, Sched Start, PIR, TF_InWindow

11/21/2014, 3, Dr B, 08:00, 11/21/2014 07:00, 1 // 1st case of day falling within window

11/21/2014, 1, Dr B, 08:00, 11/21/2014 08:00, 0 //subsequent case of day falling within window

11/21/2014, 2, Dr B, 08:00, 11/21/2014 07:50, 0 //subsequent case of day falling within window

I really appreciate your help; I'm under a tight deadline and know we're really close