topic Re: list of day to interval in App Development

list of day to interval

ThibaultL — Thu, 21 Jul 2022 12:16:18 GMT

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

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

Re: list of day to interval

hic — Fri, 22 Jul 2022 07:47:04 GMT

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

Re: list of day to interval

MarcoWedel — Sun, 24 Jul 2022 12:52:22 GMT

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;

Re: list of day to interval

sidhiq91 — Sun, 24 Jul 2022 13:54:54 GMT

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

Re: list of day to interval

MarcoWedel — Tue, 26 Jul 2022 20:30:15 GMT

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.

Re: list of day to interval

sidhiq91 — Wed, 27 Jul 2022 01:49:53 GMT

@MarcoWedel Thank you so much. I appreciate it.