Time inteval

[Varun1]

Hello got the following data:

Case Folder Name	Comments	Status	Time
1	#1 - Approve : 6/17/2020~ 4:00:00 PM	Complete	17/06/2020 4:00:00 PM
1	#1 - Reject : 6/15/2020~ 2:00:00 PM	Rejected	15/06/2020 2:00:00 PM
1	#1 - Send For Review : 6/15/2020~ 1:00:00 PM	Pending	15/06/2020 1:00:00 PM
2	#1 - Approve by : 6/16/2020~ 10:07:42 PM	Pending	16/06/2020 10:07:42 PM
2	#1 - Send For Review by : 6/15/2020~ 11:53:39 AM	Pending	15/06/2020 11:53:39 AM

What I am trying to accomplish is when the Status is 'Rejected' I want to get the time interval between the Row with the "Comment" 'Send For Review' and the "Comment" 'Approve' with the same Case Folder name

Required output:

Case Folder Name	Comments	Status	Time	Time_Diff
1	#1 - Approve : 6/17/2020~ 4:00:00 PM	Complete	17/06/2020 4:00:00 PM	2 03:00:00
1	#1 - Reject by : 6/15/2020~ 2:00:00 PM	Rejected	15/06/2020 2:00:00 PM	0 01:00:00
1	#1 - Send For Review : 6/15/2020~ 1:00:00 PM	Pending	15/06/2020 1:00:00 PM	00
2	#1 - Approve : 6/16/2020~ 10:07:42 PM	Pending	16/06/2020 10:07:42 PM	1 10:14:03
2	#1 - Send For Review : 6/15/2020~ 11:53:39 AM	Pending	15/06/2020 11:53:39 AM	00

Currently using expression:

if("Case Folder Name" = previous("Case Folder Name"),Interval(Time-Previous(Time),'D hh:mm:ss') as Time_Diff

Please advice a correct solution.

Thanks in Advance

[MayilVahanan]

Hi @Varun1 

Try like below

Test:
LOAD *, pick(WildMatch(Comments,'*send*','*reject*','*approve*'),1,2,3) as ProcessNum INLINE [
Case Folder Name, Comments, Status, Time
1, #1 - Approve : 6/17/2020~ 4:00:00 PM, Complete, 17/06/2020 4:00:00 PM
1, #1 - Reject : 6/15/2020~ 2:00:00 PM, Rejected, 15/06/2020 2:00:00 PM
1, #1 - Send For Review : 6/15/2020~ 1:00:00 PM, Pending, 15/06/2020 1:00:00 PM
2, #1 - Approve by : 6/16/2020~ 10:07:42 PM, Pending, 16/06/2020 10:07:42 PM
2, #1 - Send For Review by : 6/15/2020~ 11:53:39 AM, Pending, 15/06/2020 11:53:39 AM
];

Final:
Load *, if(ProcessNum = 1, 0, if([Case Folder Name] = Previous([Case Folder Name]), Interval(Time-Previous(Time),'D hh:mm:ss'))) as TimeDiff Resident Test order by [Case Folder Name],ProcessNum;

DROP Table Test;

[Varun1]

Hi ,

Thank you for your response. I tried your solution however, it has the same issue as my expression.As shown in the Attached excel the Time Diff shown on the first row is the interval between the  'Approve' and 'Reject' row i.e. Row no 1 and 2. What i want is to get the interval between the 'Approve' and 'Send' Row i.e. Row no 1 and Row no 3.

Please let me know if you have a solution.

Thanks

[MayilVahanan · Accepted solution]

Hi @Varun1 

Try like below

Test:
LOAD *, pick(WildMatch(Comments,'*send*','*reject*','*approve*'),1,2,3) as ProcessNum INLINE [
Case Folder Name, Comments, Status, Time
1, #1 - Approve : 6/17/2020~ 4:00:00 PM, Complete, 17/06/2020 4:00:00 PM
1, #1 - Reject : 6/15/2020~ 2:00:00 PM, Rejected, 15/06/2020 2:00:00 PM
1, #1 - Send For Review : 6/15/2020~ 1:00:00 PM, Pending, 15/06/2020 1:00:00 PM
2, #1 - Approve by : 6/16/2020~ 10:07:42 PM, Pending, 16/06/2020 10:07:42 PM
2, #1 - Send For Review by : 6/15/2020~ 11:53:39 AM, Pending, 15/06/2020 11:53:39 AM
];

Final:

Load *, if(ProcessNum = 1, 0, if([Case Folder Name] = Previous([Case Folder Name]),
if(ProcessNum=3,Interval(Time-Previous(Time)+Peek('TimeDiff'),'D hh:mm:ss'),
Interval(Time-Previous(Time),'D hh:mm:ss')))) as TimeDiff Resident Test order by [Case Folder Name],ProcessNum;

DROP Table Test;

[Varun1]

Great! This works thank you so muchh!! @MayilVahanan 😁