Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 Date | Appointment Type | Result (Followup within 90 days) |
---|---|---|---|
1 | 1/1/2010 | Initial | Yes |
1 | 2/2/2010 | Unrelated | n/a |
1 | 3/2/2010 | Followup | n/a |
1 | 4/4/2013 | Initial | No |
1 | 5/5/2013 | Unrelated | n/a |
1 | 10/10/2013 | Followup | n/a |
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
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
Thank you VERY much Henric, I was having a brain fade and your responce got me back on track. The use of the exists clause was particularly helpful.
As an aside I could not use SQL as I using an Object Orientated Database and the ODBC connector is very basic, with limited functionality, (pretty much select and group).