Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have a log table that I need to manipulate.
Essentially it shows sequential time events of processes for various accounts and I need to calculate the time difference between each process for each account;
Here is what It looks like and what I need to get out of it:
AccountID TimeStamp ProcessID
A 5/13/2016 9:10:55AM Rec.
A 5/13/2016 9:11:55 AM Review
B 5/13/2016 9:12:25 AM Rec.
C 5/13/2016 9:12:35 AM Rec.
D 5/13/2016 9:12:40 AM Rec.
A 5/13/2016 9:15:45 AM Processed
B 5/13/2016 9:16:20 AM Review
C 5/13/2016 9:18:15 AM Review
B 5/13/2016 9:25:20 AM Processed
D ..... .....
I need to calculate the elapsed time
AccountID TimeStamp ProcessID # Seconds From
Previous Process
A 5/13/2016 9:10:55AM Rec. 0
A 5/13/2016 9:11:55 AM Review 60
B 5/13/2016 9:12:25 AM Rec. 0
C 5/13/2016 9:12:35 AM Rec. 0
D 5/13/2016 9:12:40 AM Rec. 0
A 5/13/2016 9:15:45 AM Processed 230
B 5/13/2016 9:16:20 AM Review 265
C 5/13/2016 9:18:15 AM Review 340
B 5/13/2016 9:25:20 AM Processed 540
D .... .....
Any Idea of how to achieve that?
Thanks
Try this:
Table:
LOAD * Inline [
AccountID, TimeStamp, ProcessID
A, 5/13/2016 9:10:55 AM, Rec.
A, 5/13/2016 9:11:55 AM, Review
B, 5/13/2016 9:12:25 AM, Rec.
C, 5/13/2016 9:12:35 AM, Rec.
D, 5/13/2016 9:12:40 AM, Rec.
A, 5/13/2016 9:15:45 AM, Processed
B, 5/13/2016 9:16:20 AM, Review
C, 5/13/2016 9:18:15 AM, Review
B, 5/13/2016 9:25:20 AM, Processed
];
FinalTable:
LOAD *,
If(AccountID = Previous(AccountID), Interval(TimeStamp - Previous(TimeStamp), 'ss'), 0) as #Seconds
Resident Table
Order By AccountID, TimeStamp;
DROP Table Table;
Try this:
Table:
LOAD * Inline [
AccountID, TimeStamp, ProcessID
A, 5/13/2016 9:10:55 AM, Rec.
A, 5/13/2016 9:11:55 AM, Review
B, 5/13/2016 9:12:25 AM, Rec.
C, 5/13/2016 9:12:35 AM, Rec.
D, 5/13/2016 9:12:40 AM, Rec.
A, 5/13/2016 9:15:45 AM, Processed
B, 5/13/2016 9:16:20 AM, Review
C, 5/13/2016 9:18:15 AM, Review
B, 5/13/2016 9:25:20 AM, Processed
];
FinalTable:
LOAD *,
If(AccountID = Previous(AccountID), Interval(TimeStamp - Previous(TimeStamp), 'ss'), 0) as #Seconds
Resident Table
Order By AccountID, TimeStamp;
DROP Table Table;
As Stefan said, there is nothing ever to teach you!!!
You are one of my best and favorite when I post a question. (One of... Stefan is one too, whenever he takes on my challenges)
All the best.
Thanks
(And PS, do you ever sleep?)
Now everyone is going to start pulling my leg. I think I need to take a break from the community now .
Yes, I do sleep
Don't you dare taking a break. We need you !