topic Generating conditional list of outputs in QlikView

Generating conditional list of outputs

Tue, 05 Feb 2013 22:25:14 GMT

Hello,

I am releatively new to QlikView and have 2 questions:

(1) I was wondering how to write an expression that generates an output list conditional on certain fields being either available or unavailable in the data.

For example, I need to generate an output list for today's date (01/07/2013) for any requests (sample below) that do not have a visit date within 3 day of the request date AND do have both a processing date and an appointment date BUT do not have a completed date.

Today - 01/07/2013

Request #1 - AAA

Request date - 01/01/2013

Processing date - 01/01/2013

Appointment date - no data

Visit date - no data

Complete date - no data

Request #2 - BBB

Request date - 01/01/2013

Processing date - 01/01/2013

Appointment date - 01/02/2013

Visit date - no data

Complete date - 01/07/2013

Request #3 - CCC

Request date - 01/01/2013

Processing date - 01/01/2013

Appointment date - 01/02/2013

Visit date - no data

Complete date - no data

In this example, the only output in the list should be Request #3 - CCC.

(2) In additon, I need to generate a "missing data" rate for any requests completed on today's date but that are missing dates for the appointment date and visit date. In this case, the missing data rate should be 100% because the only request completed on 01/07/2013 is missing a visit date. The expression i am currently using is:

Where Dimension is Complete Date .... =(count(if(Appointment Date]=null() or Visit Date = null(),[Request #],null()))/count([Request #]))

Please advise.

Thanks for the help!!

Re: Generating conditional list of outputs

Tue, 05 Feb 2013 23:07:59 GMT

Following your logic for #1, wouldn't both Request 1 and Request 3 be shown? I don't have time today to look at #2, but take a look at the attached sheet and see if it gets you any closer.

Re: Generating conditional list of outputs

Wed, 06 Feb 2013 17:38:46 GMT

Hi Rebecca,

Thanks for your swift response!

I've been trying to work with the sheet you attached and am still having trouble. Also, I may have not been as clear as I would have liked in the instructions...Request 1 should not be shown because it did not have an appointment date.

My primary confusions surrounds what this part of the fuction is supposed to do:

(VisitDate>(RequestDate+3) or VisitDate<(RequestDate+3) or IsNull(RequestDate))

I tried to iterate on your expression, and this got me a little closer to the answer, but i'm still scrambling a bit! 🙂 On change I made is to make the look-up date for the data the Request Date (01/01/2013) rather than Today (01/07/2013):

=if([Request Date]+3)

and

(IsNull[Visit Date])

and

([Processing Date]<>0 or [Processing Date]<>'')

and

([Appointment Date]<>0 or [Appointment Date]<>'')

and

(IsNull [Complete Date]=-1 or [Complete Date]=''),1)

Thanks again, Rob

Re: Generating conditional list of outputs

Wed, 06 Feb 2013 18:05:11 GMT

Ah, I see. Take a look at the attached document. Item #3 is the only one shown.

Re: Generating conditional list of outputs

Wed, 06 Feb 2013 21:26:11 GMT

I finally got some time to play around with your second request. See if this is what you had in mind for the missing data percentage. Hope it helps!

Re: Generating conditional list of outputs

Mon, 18 Feb 2013 22:23:54 GMT

Thanks for the help, Rebecca!