How to split an overnight into two consecutive days?

nezuko_kamado — Wed, 15 Jun 2022 18:17:51 GMT

How to create new rows that split from one overnight into two consecutive days with the rest of columns(variables) remained as same, and a new column(Date) added.

Dat:
load * inline [ID, Location, Approval, Start Time, End Time
a, North, Y, 10/1/2022 10:00, 10/1/22 12:00
a, South, N, 10/1/2022 10:00, 10/1/22 12:00
b, North, Y, 10/1/2022 09:00, 10/1/22 15:30
c, North, Y, 10/1/2022 18:00, 10/2/22 02:25
d, South, Y, 10/2/2022 16:00, 10/3/22 05:00
e, South, Y, 10/3/2022 04:00, 10/3/22 11:00];

Re: How to split an overnight into two consecutive days?

rwunderlich — Wed, 15 Jun 2022 23:58:56 GMT

How about:

SET TimestampFormat='M/D/YYYY h:mm';
Dat:
Load
ID,Location, Approval,
if(Offset = 0, [Start Time], TimeStamp(DayStart([Start Time] + Offset))) as [Start Time],
TimeStamp(RangeMin([End Time], DayEnd([Start Time] + Offset))) as [End Time]
;
Load
*,
IterNo()-1 as Offset
inline [
ID, Location, Approval, Start Time, End Time
a, North, Y, 10/1/2022 10:00, 10/1/22 12:00
a, South, N, 10/1/2022 10:00, 10/1/22 12:00
b, North, Y, 10/1/2022 09:00, 10/1/22 15:30
c, North, Y, 10/1/2022 18:00, 10/2/22 02:25
d, South, Y, 10/2/2022 16:00, 10/3/22 05:00
e, South, Y, 10/3/2022 04:00, 10/3/22 11:00
] While Floor([Start Time] + IterNo()-1) < [End Time]
;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

topic Re: How to split an overnight into two consecutive days? in App Development

How to split an overnight into two consecutive days?

Re: How to split an overnight into two consecutive days?