Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

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

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;

18 Replies
Highlighted
Valued Contributor

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

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

Highlighted

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

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

Highlighted
Not applicable

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

Absolutely!

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

DateSURGEONRoomPIRFlagSched_Start
11/21/2014Dr B111/21/2014 08:00011/21/2014 08:00
11/21/2014Dr B211/21/2014 07:50011/21/2014 08:00
11/21/2014Dr B311/21/2014 07:00111/21/2014 08:00
11/21/2014Dr C411/21/2014 07:02111/21/2014 08:00
11/21/2014Dr C511/21/2014 07:56011/21/2014 08:00
Highlighted
Not applicable

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

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

DateSURGEONRoomPIRFlagSched_StartFlagMinflagPIR
11/21/2014Dr B111/21/2014 08:00111/21/2014 08:0010
11/21/2014Dr B211/21/2014 07:50111/21/2014 08:0010
11/21/2014Dr B311/21/2014 07:00111/21/2014 08:0010
11/21/2014Dr C411/21/2014 07:02111/21/2014 08:0010
11/21/2014Dr C511/21/2014 07:56111/21/2014 08:0010
Highlighted

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

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

Highlighted
Not applicable

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

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

Highlighted

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

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

Highlighted
Not applicable

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

Hi Peter,

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

Partner
Partner

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

Hi Karen,

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

Capture.PNG.png

Best regards,

Giacomo