Multirow formula with multi column criteria

[tonypreece]

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 ID	Date	Time	Action
12345	30/09/2019	16:40:32.123	A1
12345	30/09/2019	16:41:12.256	Call
12345	30/09/2019	16:42:59.556	A2
12345	30/09/2019	08:12:46.258	A3

[Hannah_Lissaman · Accepted solution]

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

[tonypreece]

Thanks Hannah, that's really useful. Today is my first time using Alteryx so I still have a lot to figure out.

I've built the workflow, but I'm struggling with the join. I've used:
> Join by specific fields
> CID = CID
> Date = Date

I've also checked all the fields in the table below the join dialogue.

When I click back onto my workflow the annotation doesn't show under the join icon (I have annotations turned on and they show under all the other icons), so I'm wondering what I'm doing wrong to stop it from working.  Unfortunately, because I'm on a work PC I can't upload a screenshot to show you. Any ideas?

Perseverance is my friend! I have it working now, thanks for providing the solution, Hannah.