To create a list of all absent days for a given employee, you could probably work with a master calendar and use some expression checks to draw the Sick, public holiday and paid holiday. I played a little bit with this using advanced agreggation, but I think this is probably not performant enough.
I assume we could do all the stuff you want in the script, since the flagging should only be quite static, right?
So I decided to create for each employee a list of all days he was absent, so I get one big table, duplicating public holidays, but I believe there are not so many and they should compress quite well in memory.
After you have one big table, you need to go through the list of absent day two times, one time ordered by ID and Date descending, second ID and Date ascending (because I work with peek() function to access the previous record, but I want to check the days in the future and in the past as well).
Note that I didn't check for change in ID here, because I believe if you have at least 2 public holidays spread over the year, you are getting some kind of "natural" delimiter.
So my script looks like
LOAD ID, Type, Date, Weekday(Date) as Weekday INLINE [
ID, Type, Date,
1, Paid Holiday, 12/8/11
2, Sick, 12/9/11
2, Sick, 12/12/11
2, Paid Holiday, 12/13/11
3, Sick, 3/2/12
3, Paid Holiday, 3/5/12
4, Sick, 3/5/12
4, Paid Holiday, 3/2/12
load distinct ID resident SICK;
join (RESULT) LOAD Date, Holiday, WeekDay(Date) as Weekday, 'Public Holiday' as Type INLINE [
12/25/11, Christmas Day
12/26/11, Boxing Day
Concatenate LOAD * resident SICK;
drop table SICK;
if(Type='Sick' and (peek(Type)='Paid Holiday' or peek(Type)='Public Holiday') and (peek(Date)-Date=1 or (peek(Date)-Date=3 and Weekday=4)),1) as FlagDesc
Resident RESULT order by ID,Date Desc;
LOAD ID, Date, Type, Holiday, Weekday,
if(Type='Sick' and (peek(Type)='Paid Holiday' or peek(Type)='Public Holiday') and (Date-peek(Date)=1 or (Date-peek(Date)=3 and Weekday=0)),1,FlagDesc) as Flag
Resident FLAG order by ID,Date;
drop tables RESULT, FLAG;
See also attached.
comm46003.qvw 138.2 K
This is a fairly amazing reply., Stefan! I'm sure the logic is correct but Im having difficulty integrating it into my dashboard, probably as my design technique isnt refined as it will be .My data is coming from a few seperate excel files so its a little harder than I thought, I would love your help.
to upload.qvw 232.0 K