Find the continuous dates of the employee with status field

[tulasiram_bitra]

I have data like below. In this , I need to avoid employees who has taken continuous 3 days sick leave and display hours in bar chart. I have attached qvw and excel sheet also.

How to get employee data who has taken sick leave for 3 continuous days  within the week only.

[tulasiram_bitra · Accepted solution]

Set DateFormat = 'DD-MM-YYYY' ;

Table_1:
Load * ,
WeekStart(Date) AS WeekStart;

LOAD Empid,
Activity,
Hours,
Date(Date#(Date, 'DD-MM-YYYY'), 'DD-MM-YYYY') as Date1,
Date,
Week(Date)&'-'&Year(Date) as Week_Year
FROM
[\\proacxfs-01\Homes\id050756\Desktop\Tula_Practice\HR.xlsx]
(ooxml, embedded labels, table is Sheet1);

Table_2:
LOAD
*,
If(Activity = 'sick'
and Activity = Previous(Activity)
and WeekStart = Previous(WeekStart)
and Date = DayStart(Previous(Date), 1),
RangeSum(Peek('Flag'), 1), 1) as Flag

Resident Table_1
Order By Empid;

Left Join (Table_1)
LOAD
Empid,
WeekStart,
If(Max(Flag) <= 2, 'Consider', 'Avoid') as FinalFlag
Resident Table_2

Group By Empid, WeekStart ;
DROP Table Table_2;