How to split an overnight into two consecutive days?

[nezuko_kamado]

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

[rwunderlich · Accepted solution]

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