Matching In and Out Events Using Multi-Row

[JessicaXChung]

Hi everyone, I'm having trouble wrapping my heading around using a multi-row formula. I have a list of truck moves that come in to pick up an item and go out to deliver the item and I need to calculate the average time of trips. 

Data(Alteryx file attached)

For my multi-row formula I need to go to a specific In event, check all the events that happen later to find an Out event where the same truck has the same item. This is the main focus but there are a couple things in the data:

• There are anomalies where there can be more than one In or Out event for the same trip
  • If there is more than one In event - Take the earliest In event as the start time
  • If there is more than one Out event - Take the latest Out event as the end time

The problem I see is that I have sort the entire dataset(300+ records) by time ascending to find the following event but I need to check all events after instead of only Row+1/Row+2. Couple things I'm thinking of doing

• Sort by truck and time so that the events for each truck are next to each other to use Row+1

[Dina]

Doesn't has the exact output you have. Hopefully this is what you need. 

[RobertOdera]

Hi, @JessicaXChung 

Kindly consider the below.

A multi-row tool might not be optimal where the data has an incomplete trip, IMHO.

I hope you find it helpful - Cheers!