topic Time calculations in QlikView

Time calculations

jduenyas — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Time calculations

sunny_talwar — Fri, 13 May 2016 19:06:32 GMT

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;

Re: Time calculations

jduenyas — Fri, 13 May 2016 19:11:07 GMT

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

Re: Time calculations

sunny_talwar — Fri, 13 May 2016 19:14:03 GMT

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

Re: Time calculations

jduenyas — Fri, 13 May 2016 19:16:26 GMT

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