Grouping Ranges of dates together

[gavin_kite]

Hi 

I'm having trouble figuring this one out and feels like it should be relatively simple

I'm trying to report on absence and more specifically, the number of occurrences. So if an employee was off from 1 Feb to 3rd of Feb, this would be 1 occurrence with 3 days in it. 

The raw data would look something like this

EMPLOYEE      Date

A                          04/02/2019

A                          05/02/2019

A                          06/02/2019

A                          12/02/2019

B                         17/01/2019

B                         18/01/2019

B                         21/01/2019

B                         22/01/2019

B                        30/01/2019

In Summary, Employee A would have 2 occurrences, the first lasting 3 days, the second lasting 1 day

Employee B would also have 2 occurrences, the first lasting 4 days (but slightly more complicated as it spans over a weekend) and the second occurrence of 1 day

Feels like I need to somehow group the dates together, using peek but I'm just not sure how to group in this way. Anyone done anything similar before?

Thanks

[gavin_kite]

I'm having trouble figuring this one out and feels like it should be relatively simple

I'm trying to report on absence and more specifically, the number of occurrences. So if an employee was off from 1 Feb to 3rd of Feb, this would be 1 occurrence with 3 days in it. 

The raw data would look something like this

EMPLOYEE      Date

A                          04/02/2019

A                          05/02/2019

A                          06/02/2019

A                          12/02/2019

B                         17/01/2019

B                         18/01/2019

B                         21/01/2019

B                         22/01/2019

B                        30/01/2019

In Summary, Employee A would have 2 occurrences, the first lasting 3 days, the second lasting 1 day

Employee B would also have 2 occurrences, the first lasting 4 days (but slightly more complicated as it spans over a weekend) and the second occurrence of 1 day

Feels like I need to somehow group the dates together, using peek but I'm just not sure how to group in this way. Anyone done anything similar before?

Thanks

[sunny_talwar]

So, in your example, can you explain why does A have 2 absent occurrences? Also, can you explain how did you get 3 and 1?

[pradosh_thakur]

Try Below

load * , if(peek(EMPLOYEE) <> EMPLOYEE ,1 ,if( peek(EMPLOYEE) = EMPLOYEE and NetWorkDays(peek(Date),Date)= 2 ,peek(occur) ,peek(occur) +1 ) )as occur Inline [
EMPLOYEE,Date

A,04/02/2019

A,05/02/2019

A,06/02/2019

A,12/02/2019

B,17/01/2019

B,18/01/2019

B,21/01/2019

B,22/01/2019

B,30/01/2019
];

[pradosh_thakur]

now jsut use the max(occur) as the expression and it will give you the occurence