Hi,
I have a situation where I'm trying to select one record out of many based on the date of an appointment.
I have for example the following two tables (in csv files) involving students having appointments. The first contains data about the student, the second about the appointment:
STUDENT:
| Student ID | ID/Instance | Course | Start date | End date |
|---|
| ABC | ABC/1 | BSc Mathematics | 1/8/2000 | 31/7/2003 |
| ABC | ABC/2 | MSc Mathematics | 1/8/2003 | 31/7/2004 |
| ABC | ABC/3 | PhD Mathematics | 1/8/2004 | 31/7/2007 |
APPOINTMENT:
| Appointment ID | Appointment Date | Student ID |
|---|
| 1 | 1/1/2002 | ABC |
| 2 | 1/1/2005 | ABC |
What I need to do is find a way to connect the Appointment ID to the most appropriate ID/Instance based on the date of the appointment. I.e. Appointment 1 took place while Student ABC was studying the BSC Mathematics, while Appointment 2 took place while ABC was studying the PhD Mathematics.
So I want a table like this:
APPOINTMENT:
| Appointment ID | Appointment Date | Student ID | Student ID/Instance |
|---|
| 1 | 1/1/2002 | ABC | ABC/1 |
| 2 | 1/1/2005 | ABC | ABC/3 |
This would enable me to analyse appointment data based on the circumstances of the student at the time of the appointment. A simple association based on Student ID however gives me something like this:
| Appointment ID | Appointment Date | Student ID | Student ID/Instance |
|---|
| 1 | 1/1/2002 | ABC | ABC/1 |
| 1 | 1/1/2002 | ABC | ABC/2 |
| 1 | 1/1/2002 | ABC | ABC/3 |
| 2 | 1/1/2005 | ABC | ABC/1 |
| 2 | 1/1/2005 | ABC | ABC/2 |
| 2 | 1/1/2005 | ABC | ABC/3 |
How would I do this in QlikView?
Thanks.
