3 Replies Latest reply: Feb 2, 2012 11:40 AM by Stefan Wühl RSS

    Date Set Union and Weekday Analysis

      Long winded one this

       

      1)

       

      I have a table of EmployeeIDs and Dates which logs the dates the employee was absent.

       

      For example

       

      IDTypeDateWeekday
      2Sick12/9/11Fri
      2Sick12/12/11Mon

      2

      Paid Holiday12/13/11Tues
      3Sick.....
      4Sick3/4/5xxx

       

      When I click in a lostbox of my employees, I can see all instances of their sickness and paid holidays. Perfect.

       

      I have another table of public holidays (not conclusive).

       

      DateHoliday
      12/25/11Christmas Day
      12/26/11Boxing Day

       

      I'd like to produce a list of all days the employee was sick, paid holiday and public holidays too. this is essentially unioning a fixed table of public holidays with a selection-dependant table of sick/paid holidays. I tried a few things in editing the dimension with no luck, I think Im getting the syntax wrong. Something along the lines of currentlySelected(Date) + (<isPublicHoliday=1> Date)

       

       

      Anyway Part 2).

       

      So lets look at Employee 2. Poor guy was off sick on Friday, Monday, and then took some holiday time Tuesday. I'd like to highlight a SICK event which is immediately adjacent to a PAIDHOLIDAY or PUBLICHOLIDAY date. So if  theres a holiday on Wednesday and hes sick on Tuesday, the Tuesday event will get flagged.

       

      ExtraBonus points for highlighting the day if the public holiday is Monday and hes sick on the Friday 3 days before.

       

      I know this hard and quite specific but Ive been trying for ages!!

        • Re: Date Set Union and Weekday Analysis
          Stefan Wühl

          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

           

          SICK:

          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

          ];

           

          RESULT:

          load distinct ID resident SICK;

           

          join (RESULT) LOAD Date, Holiday, WeekDay(Date) as Weekday, 'Public Holiday' as Type INLINE [

          Date,    Holiday

          12/25/11,    Christmas Day

          12/26/11,    Boxing Day

          ];

           

          Concatenate LOAD * resident SICK;

           

          drop table SICK;

           

          FLAG:

          LOAD *,

          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.

           

          Regards,

          Stefan