Identify elapsed time from two records sharing the same key

[mariraj]

The input data looks like below.

Req_no|Type|Time
1000|Request|2019-04-10T11.21.46.455Z
1000|Response|2019-04-10T11.22.46.466Z
2000|Request|2019-04-10T11.01.46.455Z
2000|Response|2019-04-10T11.02.06.466Z

I am trying to calculate the elapsed time (round off to seconds) between the request and response. The expected output should be as follows.
Req_no|Elapsed_Time
1000|60
2000|20

Appreciate your help with this.

[adonio · Accepted solution]

Try this search anywhere, and apply the same formula on your data.
note: there are many other ways to accomplish this goal

| makeresults count=1
| eval data="1000|Request|2019-04-10T11.21.46.455Z;;;1000|Response|2019-04-10T11.22.46.466Z;;;2000|Request|2019-04-10T11.01.46.455Z;;;2000|Response|2019-04-10T11.02.06.466Z;;;3000|Request|2019-04-10T11.21.46.455Z;;;3000|Response|2019-04-10T11.22.46.466Z;;;4000|Request|2019-04-10T11.01.46.455Z;;;4000|Response|2019-04-10T11.02.06.466Z"
| makemv delim=";;;" data
| mvexpand data
| rex field=data "(?<Req_no>[^\|]+)\|(?<Type>[^\|]+)\|(?<Time>[^\|]+)"
| table Req_no Type Time
| rename COMMENT as "the above generates data below is the solution" 
| eval epoch_time = strptime(Time, "%Y-%m-%dT%H.%M.%S.%3N")
| eval req_time = if(Type=="Request",epoch_time,null())
| eval res_time = if(Type=="Response",epoch_time,null())
| stats values(req_time) as request_time values(res_time) as response_time by Req_no
| eval Elapsed_Time = round(response_time - request_time)

hope it helps

[mariraj]

Works like a charm. Thanks for your help with it.