Help flagging minimum date (script below) - need urgent help

[#1]

Hello,

I've been wrestling with this for days after a deadline - and cannot figure out a solution though I think it's probably simple.

I want to show the following:

1. All data included in "Raw" (below)

2. Flag the minimum datetime

3. Add a conditional statement that states if the PIR (pt in room) was between 6:55 and 8:35 a.m.

What I'm getting currently with PEEK function is flagging all the PIR times as 1 instead of just the min time.

Much appreciation!

SET DateFormat='MM/DD/YYYY';

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

Raw:

LOAD * INLINE [

    Date, Room, SURGEON, Sched_Start, PIR

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

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

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

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

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

];

Data:

LOAD

Date,

Room,

SURGEON,

Sched_Start,

PIR,

If(Peek(PIR) <> PIR, 1, 0) AS Flag

RESIDENT Raw

ORDER BY SURGEON DESC;

[puttemans]

Could you explain what the output would need to be with an example?

[alexandros17]

Data:

LOAD

Date,

Room,

SURGEON,

Sched_Start,

PIR,

If(Peek(PIR) <> PIR, 1, 0) AS Flag,

If(Time(PIR) >= '6:55' and Time(PIR) <= '8:55', 1, 0) as flagPIR

RESIDENT Raw

ORDER BY SURGEON DESC;

left join

Load Min(Date) as Date, '1' as FlagMin resident Data;

let me know

[#4]

Absolutely!

So with the data set provided, it is merely adding another column called "Flag" with binary numbers (1=yes)

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

[#5]

Alessandro,

Thank you very much for your help!

So this is the output with your script.

I only want to show 1 column of "Flag" that takes into account all conditions.

It would only show a value of "1" for the earliest PIR - for Dr. B would be the 07:00 and for Dr. C would be the 07:02

Date	SURGEON	Room	PIR	Flag	Sched_Start	FlagMin	flagPIR
11/21/2014	Dr B	1	11/21/2014 08:00	1	11/21/2014 08:00	1	0
11/21/2014	Dr B	2	11/21/2014 07:50	1	11/21/2014 08:00	1	0
11/21/2014	Dr B	3	11/21/2014 07:00	1	11/21/2014 08:00	1	0
11/21/2014	Dr C	4	11/21/2014 07:02	1	11/21/2014 08:00	1	0
11/21/2014	Dr C	5	11/21/2014 07:56	1	11/21/2014 08:00	1	0

[alexandros17]

Se the part

left join

Load Min(Date) as Date, '1' as FlagMin resident Data

must be rewritten as:

left join

Load Min(Date) as Date, '1' as FlagMin resident Data group bu SURGEON;

THis will give you a atable with min date for each doctor, now you can reload the table and set an if condition on both flags, in order to compute a final flag with all conditions

[#7]

I added what you wrote but I'm confused about how to combine the FlagMin and flagPIR.

In the output currently, it is showing FlagMin = 1, which I understand as the cases fall b/w 7-8:30.

What I don't understand is flagPIR =0 for all cases because only 1 case for each Dr is first case in the morning.

Sorry if I'm missing something...thank you

[Peter_Cammaert]

How about this:

Data:

LOAD Date,

     Room,

     SURGEON,

     Sched_Start,

     PIR,

     If ((Time(PIR) >= '6:55') AND (Time(PIR) <= '8:55'), 1, 0) AS FlagBetween7and9,

RESIDENT Raw

LEFT JOIN(Data)

LOAD Min(PIR) AS PIR,

     SURGEON

     1 AS FlagMin

RESIDENT Raw

GROUP BY SURGEON;

Best,

Peter

[#9]

Hi Peter,

That did the trick! Many thanks to you - have a great day

[giacomom]

Hi Karen,

as an alternative you can do it from the front-end with the expression below:

Best regards,

Giacomo