Extract time difference between same user records in a table

Wed, 25 Nov 2020 16:16:04 GMT

I have this requirement to find the the time difference between two specific records . There are two parts into this requirement

I have to take single user and find out the time difference between initiator timestamp and convertor timestamp and convert the above table to this

UserName Time lag

Neha jain 1 day

Neha jain 2 days

Ankit Jain 1 day

and secondly i have to count the number of users with same time lag, so final result will be

Number of users Time Lag

2 1day

1 2days

Re: Extract time difference between same user records in a table

sunny_talwar — Wed, 27 Jul 2016 19:29:51 GMT

Script:

Table:

LOAD *,

Date(Floor(Timestamp)) as Date;

LOAD * INLINE [

User Name, Position, Timestamp

Neha Jain, Initiator, 11/7/2015 6:01:00 AM

Neha Jain, Assistor, 11/7/2015 6:02:00 AM

Neha Jain, Assistor, 11/7/2015 6:03:00 AM

Neha Jain, Converter, 12/7/2015 6:04:00 AM

Neha Jain, Initiator, 13/7/2015 6:05:00 AM

Neha Jain, Assistor, 13/7/2015 6:06:00 AM

Neha Jain, Converter, 15/7/2015 6:07:00 AM

Ankit Jain, Assistor, 16/7/2015 6:08:00 AM

Ankit Jain, Initiator, 16/7/2015 6:09:00 AM

Ankit Jain, Converter, 17/7/2015 6:10:00 AM

];

FinalTable:

LOAD *,

If([User Name] = Previous([User Name]),

If(Position = 'Initiator', RangeSum(Peek('Key'), 1), Peek('Key')), 1) as Key

Resident Table

Where Match(Position, 'Initiator', 'Converter')

Order By [User Name], Timestamp;

Concatenate (FinalTable)

LOAD *,

0 as Key

Resident Table

Where Match(Position, 'Assistor');

DROP Table Table;