I have a table showing peoples names and the date that thy were attended to. I am using the PREV statement in the script to look up the date difference between a previous incident and that incident.
What has been pointed out though is that users want to see if the incident resulted in a follow up attendance.
so instead of looking for all incidents which was the follow up, I need to know which incidents were the precursor to a follow up.
If I understand your situation correctly it sounds as though you're most of the way there with the previous() function.
I'm assuming that amoungst your data table you have fields that are something similar to an 'IncidentID' and 'AttendedTime', if so the following should get you started:
Lets say the table you have is simply called 'Data' and have already loaded it in your script.
Qualify *;
Data_2:
Load *,
if(previous(IncidentID)=IncidentID,'Precursor','First/Final') as Indicator;
NoConcatenate Load
*; // Your data fields
Resident Data Order By IncidentID Asc, AttendedTime Desc;
*I haven't checked that syntax but the principal is there (I use it in the wild).
Essentially what you're doing is grouping the incidents together then ordering them with the most recent AttendedTime at the top of each group - ie the last time they were seen; this can be derived by looking at the previous IncidentID; if it's different we must be looking at a final visit (of the one and only) of a seperate incident. Likewise if the row above has the same IncidentID then we must be looking at a precursor.
You can also do the following to show how many visits there have been and the gap between first and last:
Unqualify *;
Left Join (Data_2)
Load IncidentID,
count(IncidentID) as Visits,
max(AttendedTime)-min(AttendedTime) as TotalTime;
Resident Data_2 Group By IncidentID;
As mentioned the syntax isn't checked so you may need to make some slight changes.
