Consecutive Days Calculation

[Padashwi]

Hi Team, I'm looking to perform a consecutive leave calculation of the employee. The Emp's who have 3 or more consecutive sick leaves, those emp's start and End date of the leaves to be displayed. In this scenario, their might be public holiday's in between. The start and End date of the leaves should not begin or end with the public holidays.
Attaching the input file , holiday file and Expected output file for your reference. Please suggest a solution.

[CarliE]

@Padashwi 

Attached is a workflow. 

If this solution helped to solve your problem, be sure to mark it as a solution!

Thanks and have a nice weekend!

[Qiu · Accepted solution]

@Padashwi 
Let me give a try also.

I used the Multi-row formula tool to segregate the "Groups" for the same person, and the date is consecutive.
Then we can sample the first and last date for each group and check if it is public holiday.
Join back the original data stream to rule out the public holidays as Left joint.

Then the left is easy.😁

Maybe we submit this one as an idea for weekly challenge?

[CarliE]

@Qiu definitely a good weekly challenge!! 

[Kurohits]

Hey @Padashwi, 

Another great way of doing this. Have tried to use minimum tools to reach output.