Get Employee count who are continuously absent for 10 or more days

[Shahzad_Ahsan]

Hi

I need to count employees who are continuously absent for 10 or more days. Excluding weekends, here weekend days are Fri and Sat

Sample data and qvf is attached. Please update in this qvf .

This sample data contains total 5 employees out of which 2 are absent continuously for 10 or more days. Those are,1001 and 1004. I want measure expression that count these 2 employee.

[christophebrault]

Hi,

I don't have so much time, but is this script working as desired ?

TEMP:

LOAD

    EmpCode,Month(Att_Date) as Month,

    Att_Date,num(weekday(Att_Date)) as NoDay,

    Absent

FROM [lib://Attendance/Attendance.xlsx]

(ooxml, embedded labels, table is Sheet1);

FINAL:

LOAD * ,

    if(EmpCode=Peek(EmpCode) ,

     If(NoDay<5    and Absent='Y',Peek(AbsDuration)+1,

     If(NoDay<5    and (Absent='N' or peek(Month)<>Month),0, Peek(AbsDuration))),0)

     as AbsDuration

Resident TEMP

order by EmpCode,Att_Date asc;

drop table TEMP;

[Shahzad_Ahsan]

No, This is not working.

[Shahzad_Ahsan]

I need something like this

EmpCode	Date	Absence	AbsDuration(Old)	AbsDuration(Needed)
1005	27-May-2018	Y	1	1
1005	28-May-2018	Y	2	2
1005	29-May-2018	Y	3	3
1005	30-May-2018	Y	4	4
1005	31-May-2018	Y	5	5
1005	01-Jun-2018	N	5	5
1005	02-Jun-2018	N	5	5
1005	03-Jun-2018	Y	6	1
1005	04-Jun-2018	Y	7	2
1005	05-Jun-2018	Y	8	3
1005	06-Jun-2018	Y	9	4
1005	07-Jun-2018	Y	10	5

[Shahzad_Ahsan]

Finally I achieved what I needed

TEMP:

LOAD

    EmpCode,Month(Att_Date) as Month1,

    Att_Date,num(weekday(Att_Date)) as NoDay,

    Absent

FROM [lib://Attendance/Attendance.xlsx]

(ooxml, embedded labels, table is Sheet1);

FINAL:

LOAD * ,

    if(EmpCode=Peek(EmpCode) ,if(Month1=peek(Month1),

     If(NoDay<5 and Absent='Y' ,Peek(AbsDuration)+1,

     If(NoDay<5 and Absent='N' ,0, Peek(AbsDuration))),0),0)

     as AbsDuration

Resident TEMP

order by EmpCode,Att_Date asc;

drop table TEMP;

[Mr_H_Huang]

@Shahzad_Ahsan Thank you very much for your final solution with re-set syntax. It has been my very helpful to my case.

I would like to share my simlar result of the working script. I have modifed mine based on your solution. My user requirement was:
1, there are many different roster, each has has different working date and non-working date pattern, a employee could work on one or multiple working pattern during his long employment. 

2, If an employee absence continued for 11 days or more of their working dates , it is flaged as long term absence. 

3, Non-working date not treated as absense, but will carry on to the next absence, untill the employee come back to work. It means re-set when employee back to work.

4, If employee_working_hours  > 0, it means it is a working date. If employee_working_hours = 0, it means it is a non-working date. absence_emp_no means the employee number was absence on that date. 

5, Be aware the order by is very import to sort the absence duration.

employee_date_with_absence_temp:
LOAD
absence_emp_no,
absence_reg_time,
IF(employee_working_hours >0 AND NOT ISNULL(absence_emp_no), 1, 
IF(employee_working_hours >0 AND ISNULL(absence_emp_no), 2 ,
IF((employee_working_hours = 0 OR ISNULL(employee_working_hours)) AND NOT ISNULL(absence_emp_no), 3, 0

)
)
) AS flag_type,

employee_no,
roster_ref,
employee_working_hours,
employee_working_date

RESIDENT employee_date_final
ORDER BY employee_no ASC, employee_working_date ASC;
DROP TABLE employee_date_final;


employee_date_with_absence:
LOAD
absence_emp_no,
absence_reg_time,
flag_type,

IF(employee_no=PEEK(employee_no), //if employee_no the same as the employee_no above
      IF( flag_type=1, PEEK(absence_duration)+1, //if the date is working date, and employee absence, absence_duration plus 1 day
      IF(flag_type=3 OR flag_type=0, PEEK(absence_duration), //if the date is non-working date, absence_duration remain unchange 
      IF(flag_type=2, 0, PEEK(absence_duration) )//if the employee worked on a working date during absece, absence_duration reset to 0, otherwise absence_duration remian unchange
      )),0)//All other cases re-set to zero
     AS absence_duration,

IF(
IF(employee_no=PEEK(employee_no), //if employee_no the same as the employee_no above
      IF( flag_type=1, PEEK(absence_duration)+1, //if the date is working date, and employee absence, absence_duration plus 1 day
      IF(flag_type=3 OR flag_type=0, PEEK(absence_duration), //if the date is non-working date, absence_duration remain unchange 
      IF(flag_type=2, 0, PEEK(absence_duration) )//if the employee worked on a working date during absece, absence_duration reset to 0, otherwise absence_duration remian unchange
      )),0)//All other cases re-set to zero
      >=11,1,0)
     AS long_term_absence_flag,

employee_no,
roster_ref,
employee_working_hours AS roster_working_hours,
employee_working_date AS roster_working_date

RESIDENT employee_date_with_absence_temp
ORDER BY employee_no ASC, employee_working_date ASC;
DROP TABLE employee_date_with_absence_temp;