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

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

Fri, 28 Nov 2014 14:01:55 GMT

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;

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

puttemans — Fri, 28 Nov 2014 14:06:44 GMT

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

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

alexandros17 — Fri, 28 Nov 2014 14:14:04 GMT

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

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

Fri, 28 Nov 2014 14:16:04 GMT

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

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

Fri, 28 Nov 2014 14:26:44 GMT

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

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

alexandros17 — Fri, 28 Nov 2014 14:34:09 GMT

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

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

Fri, 28 Nov 2014 14:55:43 GMT

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

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

Peter_Cammaert — Fri, 28 Nov 2014 16:00:17 GMT

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

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

Fri, 28 Nov 2014 16:37:59 GMT

Hi Peter,

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

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

giacomom — Fri, 28 Nov 2014 17:28:55 GMT

Hi Karen,

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

Best regards,

Giacomo

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

Fri, 28 Nov 2014 17:49:27 GMT

Hi Giacomo,

Thanks so much - this may be easier for me to integrate than the other which I'm too much of a newbie to make work correctly in the current load script.

One question - it's now just showing the first cases as below even though I have set the condition as 0...do you know what I'm doing incorrectly?

Date	SURGEON	Room	PIR	Min_TIR
11/21/2014	Dr B	3	11/21/2014 07:00	1
11/21/2014	Dr C	4	11/21/2014 07:02	1

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

Sat, 29 Nov 2014 13:39:26 GMT

Hi Giacomo,

Could you possibly send me your qv file so I may compare to the one I have?

Thank you so much for your help!

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

giacomom — Mon, 01 Dec 2014 08:51:24 GMT

Hi Karen,

sorry for my late response. How can I attach the file?

Giacomo

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

anbu1984 — Mon, 01 Dec 2014 09:08:41 GMT

Click "Use Advanced Editor". Then click Attach

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

giacomom — Mon, 01 Dec 2014 09:23:19 GMT

Hi Karen,

here it is my qvw file.

(Thanks anbu cheliyan, I was answering from the Inbox and the advanced editor was disabled).

Giacomo

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

Thu, 04 Dec 2014 12:40:22 GMT

Hi Giacomo,

Thank you very much; apologies for late response have been in a Kaizen Event all week.

I cannot see the attached file; could I trouble you to re-attach?

Appreciate your help!

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

anbu1984 — Thu, 04 Dec 2014 12:49:16 GMT

I can see the file. Attaching Giacomo's QVW

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

Sat, 06 Dec 2014 14:18:36 GMT

Hi Anbu,

For some reason I cannot download the file.

Are you able to send to me at karen.wallace@health-first.org?

Appreciate your help!

Thank you,

Karen

Karen Wallace, MPH

Sr. Decision Support Analyst

Analytics & Business Intelligence

Health First, Inc.

3300 Fiske Blvd.

Rockledge, FL 32955

Office: 321-434-6637

E-Mail: Karen.Wallace@Health-First.org

>>> anbu cheliyan <qcwebmaster@qlikview.com> 12/4/2014 7:50 AM >>>

Qlik Community ( http://community.qlik.com/ )

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

reply from anbu cheliyan (

http://community.qlik.com/people/anbu1984?et=watches.email.thread ) in

New to QlikView - View the full discussion (

http://community.qlik.com/message/672662?et=watches.email.thread#672662

)

I can see the file. Attaching Giacomo's QVW

Reply to this message by replying to this email, or go to the message

on Qlik Community (

http://community.qlik.com/message/672662?et=watches.email.thread#672662

)

Start a new discussion in New to QlikView by email (

mailto:discussions-community-discussion~~forums-new~~to--qlikview@qliktech-public.hosted.jivesoftware.com

) or at Qlik Community (

http://community.qlik.com/choose-container.jspa?contentType=1&containerType=14&container=2003&et=watches.email.thread

)

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

help (

http://community.qlik.com/message/672662?et=watches.email.thread#672662

) in these streams: Inbox

http://www.qlikview.com/us/info/copyright ) | Privacy (

http://www.qlikview.com/us/info/privacy ) | Terms of Use (

http://www.qlikview.com/us/info/terms-of-use ) | Software EULA (

http://www.qlikview.com/us/info/software-eula )

#####################################

This message is for the named person's use only. It may

contain private, proprietary, or legally privileged information.

No privilege is waived or lost by any mistransmission. If you

receive this message in error, please immediately delete it and

all copies of it from your system, destroy any hard copies of it,

and notify the sender. You must not, directly or indirectly, use,

disclose, distribute, print, or copy any part of this message if you

are not the intended recipient. Health First reserves the right to

monitor all e-mail communications through its networks. Any views

or opinions expressed in this message are solely those of the

individual sender, except (1) where the message states such views

or opinions are on behalf of a particular entity; and (2) the sender

is authorized by the entity to give such views or opinions.

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

anbu1984 — Mon, 08 Dec 2014 05:32:13 GMT

Emailed QVW to your mail id