This is a follow up question from my previous thread
https://community.qlik.com/t5/New-to-Qlik-Sense/Creating-start-and-end-time-from-singular-Column/m-p... Which has been solved thanks to @Vegar also allowing me to understand how peek() works
So now I have a new problem which had tried to various way to resolved but was unable to.
So this is the current data after initial transformation:
|Incident ID*+||StartDate||EndDate||Duration (hh:mm:ss)||Work Log Type||Work Log Event|
|INC000000448199||01/01/2021 9:48:02||01/01/2021 10:02:00||0:13:58||Assignment Change||Person 1|
|INC000000448199||01/01/2021 9:48:07||01/01/2021 9:53:33||0:05:26||Status Change||In Progress|
|INC000000448199||01/01/2021 9:53:33||01/01/2021 10:02:04||0:08:31||Status Change||Assigned|
|INC000000448199||01/01/2021 9:53:33||-||-||Assignment Group Change||CMCC|
|INC000000448199||01/01/2021 10:02:00||-||-||Assignment Change||Person 2|
|INC000000448199||01/01/2021 10:02:04||01/01/2021 13:53:35||3:51:31||Status Change||In Progress|
|INC000000448199||01/01/2021 13:53:35||-||-||Status Change||Resolved|
For the last few events I need them to have the "Resolved" StartDate to be in the EndDate of the other 2 Event changes in Bold. Since they should take in the resolved date time stamp as the Ticket has been resolved.
Here is my code below:
Load [Incident ID*+], [StartDate], if(isnull([EndDate]),if(peek('Incident ID*+')=[Incident ID*+],If([Work Log Event]='Resolved',StartDate)),EndDate) as EndDate, [Work Log Type], [Work Log Event] Source;
Not sure what i did wrong there.
I don't really get why you need the if(peek('Incident ID*+')=[Incident ID*+] here. Why should it be the same?
But back to the question, do you know if the field is actually null()?
Load [Incident ID*+], [StartDate], if( Trim([EndDate]) & '' = '' , if(peek('Incident ID*+')=[Incident ID*+], If([Work Log Event]='Resolved', StartDate ) ) ,EndDate ) as EndDate, [Work Log Type], [Work Log Event] Source;