Time calculations

[jduenyas]

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

[sunny_talwar · Accepted solution]

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;

[jduenyas]

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

[sunny_talwar]

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

[jduenyas]

Don't you dare taking a break. We need you !