topic Grouping Ranges of dates together in QlikView

Grouping Ranges of dates together

gavin_kite — Sat, 16 Nov 2024 04:33:35 GMT

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

Re: Grouping Ranges of dates together

sunny_talwar — Tue, 05 Feb 2019 12:30:42 GMT

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?

Re: Grouping Ranges of dates together

pradosh_thakur — Tue, 05 Feb 2019 12:32:27 GMT

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
];

Re: Grouping Ranges of dates together

pradosh_thakur — Tue, 05 Feb 2019 12:33:26 GMT

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