Multirow formula with multi column criteria

Question

I have a dataset with four columns: Customer ID [CID], Date, Time, Action Code

A new row is added to the data for every action recorded and there may be multiple actions per customer contact. I need to extract all the data for contacts made by telephone (action code 'call'). The problem is the 'call' action code might be at any point during the contact, i.e. first, last, fourth row of data for that contact.

How can I use the multirow formula tool (or any other within Alteryx Designer) to extract the data I need: Where action code = call, get all records where CID = the CID for the call row and date = the date of the call row. If it's possible, I'd also like time to be +/- 10 minutes of the call row time (see table below where the bottom row happened hours before the others so would be excluded).

Customer IDDateTimeAction1234530/09/201916:40:32.123A11234530/09/201916:41:12.256Call1234530/09/201916:42:59.556A21234530/09/201908:12:46.258A3

Hannah_Lissaman · Accepted Answer

The best way to achieve this would be using a join to identify the customers and days of actions which match the calls, then use a formula to check whether the time is within 10 mins of the call time.

To break down my workflow:

1) Filter out actions which are calls, and actions which are not

2) Join on Customer ID and Date - this will match up the details of each call with the non-call actions for the same date and customer, and exclude any customers and days which did not involve a call

3) Parse times - data cleaning

4) Formula tool - create a new column with a formula which checks whether the time of each action is within 10 mins of the call

5) Filter based on your new column to get just the actions which were within 10 mins of a call to the same customer