Creating start and end time from singular Column

[Keitaru]

Hi 

Need help with the below in to in understanding the correct logic and function to use to to create a start and end date/time.

I've already created event markers from the original raw data, I've attached the raw file below.

Current file:

Incident ID*+	Work Log Date	Work Log Type	Work Log Note
INC000000403365	17/11/2020 10:24:40	Status Change	Status Marked: Pending
INC000000403365	09/12/2020 10:57:27	Status Change	Status Marked: Assigned
INC000000403365	16/12/2020 10:56:54	Status Change	Status Marked: Pending
INC000000403365	16/12/2020 14:12:35	Status Change	Status Marked: Resolved
INC000000426007	03/12/2020 15:09:15	Status Change	Status Marked: Pending
INC000000426007	08/12/2020 13:33:16	Status Change	Status Marked: Assigned
INC000000426007	08/12/2020 15:38:50	Status Change	Status Marked: Pending
INC000000426007	10/12/2020 11:22:34	Status Change	Status Marked: Resolved
INC000000426650	04/12/2020 6:37:27	Status Change	Status Marked: Pending
INC000000426650	04/12/2020 16:28:52	Status Change	Status Marked: Assigned
INC000000426650	04/12/2020 16:29:07	Status Change	Status Marked: Pending
INC000000426650	04/12/2020 18:59:25	Status Change	Status Marked: In Progress
INC000000426650	04/12/2020 19:08:00	Status Change	Status Marked: Pending
INC000000426650	10/12/2020 11:16:44	Status Change	Status Marked: Resolved
INC000000428972	07/12/2020 16:01:51	Status Change	Status Marked: Pending
INC000000428972	16/12/2020 19:30:36	Status Change	Status Marked: Resolved
INC000000429540	08/12/2020 8:22:22	Status Change	Status Marked: In Progress
INC000000429540	08/12/2020 8:33:05	Status Change	Status Marked: Pending
INC000000429540	10/12/2020 12:20:51	Status Change	Status Marked: Resolved

idea is that we have a fix ID that goes through variation of status changes over time as shown in the work log date. Every new status change is a new DatetimeStamp recorded which is also the end DatetimeStamp for the previous status. How can I achieve this to eventually looking like the table below?

Incident ID*+	Work Log Date Start	Work Log Date End	Work Log Type	Work Log Note
INC000000403365	17/11/2020 10:24:40	09/12/2020 10:57:27	Status Change	Status Marked: Pending
INC000000403365	09/12/2020 10:57:27	16/12/2020 10:56:54	Status Change	Status Marked: Assigned
INC000000403365	16/12/2020 10:56:54		Status Change	Status Marked: Pending

[Vegar · Accepted solution]

One way to solve it is to sort your data and use peek() with conditions when loading the data. Try something like the script below.

Load

[Incident ID*+],

[Work Log Date] as [StartDate],

if(Peek('Incident ID*+')=[Incident ID*+], peek('StartDate'), null()) as EndDate,

, [Work Log Type], [Work Log Note]

From Source 

Order by [Incident ID*+], [Work Log Date] desc;

[Keitaru]

How does peek work? like to better understand it.

[Vegar]

Peek let's you peek back on previous loaded data. Take a look at this help page https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecor...

[Keitaru]

Great thanks for the help