Thanks for the advice! Sorry but I forgot to to add that the staff has to have missed providing inputs for consecutively 2 days before it is considered as a count. My bad...
Hi @Jarvay then it will be better to flag those records on script... and if the staff misses for 4 consecutive days it counts as 1, or 2 or 3?
// Cartesian of staff and dates
tmpStaffDates:
LOAD
FieldValue('Staff Name', RecNo()) as [Staff Name]
AutoGenerate FieldValueCount('Staff Name');
Outer Join (tmpStaffDates)
LOAD
FieldValue('Recorded On', RecNo()) as [Recorded On]
AutoGenerate FieldValueCount('Recorded On');
// Add values (the ones witout values will be null)
Left Join (tmpStaffDates)
LOAD [Staff Name], [Recorded On], Temperature
Resident TableDataName;
// Remove previous data table to keep the temporary as the data table
DROP Table TableDataName;
// Flag records
TableDataName:
LOAD [Staff Name], [Recorded On], Temperature,
If(Peek([Staff Name])=[Staff Name] and IsNull(Temperature) and IsNull(Peek(Temperature))
,If(Peek(isGap)=0 and not IsNull(Peek(Temperature,-2)), 1, 0) //This is to flag only the first 2 days gap
,0) as isGap
Resident tmpStaffDates
Order By [Staff Name], [Recorded On];
// Remove temporary table
DROP Table tmpStaffDates;
