Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Could you explain what the output would need to be with an example?
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
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 |
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 |
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
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
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
Hi Peter,
That did the trick! Many thanks to you - have a great day
Hi Karen,
as an alternative you can do it from the front-end with the expression below:
Best regards,
Giacomo