Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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
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;
Great! This works thank you so muchh!! @MayilVahanan 😁