Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Keitaru
Creator
Creator

Creating start and end time from singular Column

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 TypeWork Log Note
INC00000040336517/11/2020 10:24:40Status ChangeStatus Marked: Pending
INC00000040336509/12/2020 10:57:27Status ChangeStatus Marked: Assigned
INC00000040336516/12/2020 10:56:54Status ChangeStatus Marked: Pending
INC00000040336516/12/2020 14:12:35Status ChangeStatus Marked: Resolved
INC00000042600703/12/2020 15:09:15Status ChangeStatus Marked: Pending
INC00000042600708/12/2020 13:33:16Status ChangeStatus Marked: Assigned
INC00000042600708/12/2020 15:38:50Status ChangeStatus Marked: Pending
INC00000042600710/12/2020 11:22:34Status ChangeStatus Marked: Resolved
INC00000042665004/12/2020 6:37:27Status ChangeStatus Marked: Pending
INC00000042665004/12/2020 16:28:52Status ChangeStatus Marked: Assigned
INC00000042665004/12/2020 16:29:07Status ChangeStatus Marked: Pending
INC00000042665004/12/2020 18:59:25Status ChangeStatus Marked: In Progress
INC00000042665004/12/2020 19:08:00Status ChangeStatus Marked: Pending
INC00000042665010/12/2020 11:16:44Status ChangeStatus Marked: Resolved
INC00000042897207/12/2020 16:01:51Status ChangeStatus Marked: Pending
INC00000042897216/12/2020 19:30:36Status ChangeStatus Marked: Resolved
INC00000042954008/12/2020 8:22:22Status ChangeStatus Marked: In Progress
INC00000042954008/12/2020 8:33:05Status ChangeStatus Marked: Pending
INC00000042954010/12/2020 12:20:51Status ChangeStatus 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 StartWork Log Date EndWork Log TypeWork Log Note
INC00000040336517/11/2020 10:24:4009/12/2020 10:57:27Status ChangeStatus Marked: Pending
INC00000040336509/12/2020 10:57:2716/12/2020 10:56:54Status ChangeStatus Marked: Assigned
INC00000040336516/12/2020 10:56:54 Status ChangeStatus Marked: Pending

 

 

Labels (1)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

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;

View solution in original post

4 Replies
Vegar
MVP
MVP

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
Creator
Creator
Author

How does peek work? like to better understand it.

Vegar
MVP
MVP

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
Creator
Creator
Author

Great thanks for the help