I'm coming up against a bit of a mental wall at the moment and need some help getting over it.
I have a couple of tables, one comprising of a list of technician appointments (by day), one with a list of absences (with start and end dates) and a calendar.
I'm trying to tie the appointments and absences together so I can produce a simple pivot table showing all of this information but as the absences are showing a start and end date (e.g. start date of 27/12/10 and end date of 31/12/10) I think I need to break the period down to individual days.
I'm not sure how to do this and I'm trying to find some examples but struggling a bit to apply them to what I need.
Ok, so now I've got here and getting an error saying the first line isn't valid:
FOR i=1 to NoOfRows(TempAbsence) let vAbsenceDay = peek("Start Period",i); let vAbsenceEnd = peek("End Period",i); do while $(vAbsenceDay) <= $(vAbsenceEnd) load "Entry No_", "Employee No_", "Start Date", "Date Returned to Work", "Cause of Absence Code", Description, Quantity, "Unit of Measure Code", "Quantity (Base)", "AM Only", "PM Only", vAbsenceDay as DateOfAbsence, 1 as AbsenceCount; let vAbsenceDay = vAbsenceDay + 1; loop next i
Chris Johnson wrote: I have a couple of tables, one comprising of a list of technician appointments (by day), one with a list of absences (with start and end dates) and a calendar. I'm trying to tie the appointments and absences together
If I understand what you're after, this is typically solved with an intervalmatch. I don't think you'll need to break it down by day. An intervalmatch might look something like this (you should look up the help text for an explanation):
INTERVALMATCH (AppointmentDate) LOAD AbsenceStart ,AbsenceEnd FROM wherever ;
I suspect there's more to it, like adding technician:
INTERVALMATCH (AppointmentDate, Technician) LOAD AbsenceStart ,AbsenceEnd ,Technician FROM wherever ;
Also, the resulting data is often joined back to one or the other of the tables. But it's hard to know specifics without actually seeing your script and so on. But you can probably work out the details on your own. Just look for intervalmatch in the help text index.
Thanks for the tip about intervalmatch, I'll keep that in mind as I'm trying to solve this one.
I think I may have got confused when I was writing out my description. Essentially I have two lists, one of technician appointments and one with periods of absence. The two lists don't overlap so I'm not sure whether intervalmatch will help me (it might do, I just haven't worked out how yet!).
I'm trying to end up with a table with technicians down the side and days along the top, with a couple of expressions for number of appointments and whether they have an absence on that day (which is the difficulty as the absences are in periods, not individual days).
