Peek() function to display time in new column of final event changes

[Keitaru]

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.

[JordyWegman]

Hi Keitaru,

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()?

Try this:

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;

 Jordy

Climber

[Keitaru]

Hi,

Tried the code for some reason it isn't working for both anything with the [Work Log Type] = 'Assignment Change'  or  'Assignment Group Change' 

Current

Supposed:

[Keitaru · Accepted solution]

I manage to fix this myself. After some more trial and errors 

[JordyWegman]

Hi Keitaru,

Good to hear! Do you mind showing the fix maybe? It could help other people!

Jordy

Climber