5 Replies Latest reply: Feb 18, 2013 5:23 PM by rhearne1 RSS

    Generating conditional list of outputs

      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!!