Discussion board where members can get started with QlikView.
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.
SET TimestampFormat='MM/DD/YYYY hh:mm';
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
If(Peek(PIR) <> PIR, 1, 0) AS Flag
ORDER BY SURGEON DESC;
If(Peek(PIR) <> PIR, 1, 0) AS Flag,
If(Time(PIR) >= '6:55' and Time(PIR) <= '8:55', 1, 0) as flagPIR
ORDER BY SURGEON DESC;
Load Min(Date) as Date, '1' as FlagMin resident Data;
let me know
So with the data set provided, it is merely adding another column called "Flag" with binary numbers (1=yes)
|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|
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
|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
Load Min(Date) as Date, '1' as FlagMin resident Data
must be rewritten as:
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:
If ((Time(PIR) >= '6:55') AND (Time(PIR) <= '8:55'), 1, 0) AS FlagBetween7and9,
LOAD Min(PIR) AS PIR,
1 AS FlagMin
GROUP BY SURGEON;