list of day to interval

[ThibaultL]

Hi all,

I need to transform list of day off to interval of holiday like below.

employee	date
A	07-05-2022
A	07-06-2022
A	07-07-2022
A	07-31-2022
A	08-01-2022
B	06-20-2022
B	06-21-2022

to

employee	startDate	endDate
A	07-05-2022	07-07-2022
A	07-31-2022	08-01-2022
B	06-20-2022	06-21-2022

Someone can help me ?

Thanks a lot

[hic]

Take a look at https://community.qlik.com/t5/Qlik-Design-Blog/Creating-a-Date-Interval-from-a-Single-Date/ba-p/1467...

[MarcoWedel · Accepted solution]

Hi,

maybe this helps?

tabDaysOff:
LOAD *,
     RangeSum(Peek(IntervalID),-(employee<>Previous(employee) or date>Previous(date)+1)) as IntervalID;
LOAD employee,
     Date#(date,'MM-DD-YYYY') as date
Inline [
employee,	date
A,	07-05-2022
A,	07-06-2022
A,	07-07-2022
A,	07-31-2022
A,	08-01-2022
B,	06-20-2022
B,	06-21-2022
C,  05-01-2022
C,  05-02-2022
C,  05-03-2022
C,  05-04-2022
C,  05-05-2022
C,  05-06-2022
C,  05-10-2022
C,  05-11-2022
C,  05-12-2022
C,  05-20-2022
C,  05-21-2022
C,  05-22-2022
C,  05-23-2022
];

tabIntervals:
LOAD IntervalID,     
     Date(Min(date),'MM-DD-YYYY') as startDate,
     Date(Max(date),'MM-DD-YYYY') as endDate
Resident tabDaysOff
Group By IntervalID;

[sidhiq91]

@MarcoWedel  I went through your above solution. I wanted to understand what does below mentioned piece of Code does, could you please explain? Thanks in advance.

RangeSum(Peek(IntervalID),
-(employee<>Previous(employee) or date>Previous(date)+1)) as IntervalID;

[MarcoWedel]

It works similar to 

If(employee=Previous(employee) and date=Previous(date)+1, Peek(IntervalID), RangeSum(Peek(IntervalID),1)) as IntervalID;

but is a bit shorter, using the numeric value of a boolean true to calculate the increment of the IntervalID,
i.e. 0/false and -1/true.
In essence, this expression creates a new/incremented IntervalID in case the employee changes or the dates are non consecutive.

[sidhiq91]

@MarcoWedel  Thank you so much. I appreciate it.