2 Replies Latest reply: Apr 29, 2013 8:46 PM by Rob Murphy RSS

    Inter Record Calculations and Sub Queries

      Hi There,

       

      I have been trying find a Qlikview solution to this.

       

      I have a situaion where I need to check to see if a record exists in the future.  Specifically has a client had a followup appointment within 90 days.  If a client has had an initial appointment, I need to check to see if they have had a follow up within the time frame.  The same client may then come back in a year or so and go through the same process again.

       

      In pure SQL code I would write a sub query to return the values, in this case SQL is not an option, and I am struggling to come up with a solution.

       

      Any help would be appreciated.

       

       

       

       

      Client ID


      Appointment DateAppointment TypeResult (Followup within 90 days)
      11/1/2010InitialYes
      12/2/2010Unrelatedn/a
      13/2/2010Followupn/a
      14/4/2013InitialNo
      15/5/2013Unrelatedn/a
      110/10/2013Followupn/a
        • Re: Inter Record Calculations and Sub Queries
          Henric Cronström

          This is a type of problem that you may prefer to solve with SQL than in the QlikView script. Are you sure you cannot use SQL? There are drivers both for text files and for Excel files.

           

          Anyway - if I was to do it in the QlikView script, I would do something along the lines of:

           

          // === All appointments

          TempAppointments:

          Load *,

                    RecNo() as TempAppointmentID

                    From Appointments ;

           

          // === Just follow up appointments

          Left Join

          Load

                    ClientID,

                    AppointmentDate as FollowUpDate

                    resident TempAppointments

                    where AppointmentType = 'Followup';

           

          // === All appointments again

          Appointments:

          Load

                    TempAppointmentID as AppointmentID,

                    ClientID,

                    AppointmentDate,

                    FollowUpDate,

                    If(FollowUpDate - AppointmentDate > 0 and FollowUpDate - AppointmentDate <= 90, 'Yes', 'No') as HasFollowUp

                    resident TempAppointments

                    Where ( FollowUpDate - AppointmentDate > 0 or IsNull(FollowUpDate) )

                              and not Exists(AppointmentID,TempAppointmentID) // To get only one record per appointment

                    Order By AppointmentDate, FollowUpDate;

           

          Drop Table TempAppointments;

           

          HIC