Question- Is Alteryx able to join / merge two data sets based on multiple field criteria? With one field criteria being relative/ range of time. Problem - Trying to find an automated way to associate Caller Name and Call reason to our telephone records. Data set 1 is Telephone records Data set 2 is Agent records Fields in each data set are absolute in regards to Client, Start Date, Service Rep. “Start Time” in Agent records can be 0-5min after the telephone record start time; therefore not an exact match (I.e. – Telephone record has 9:01am and Agent record has 9:03am) Data set 1 Telephone Records– Client Start Date Start Time Calling Party Service Rep BBF 5/25/2017 9:10:20 AM 9707815688 Smith, John BBF 5/25/2017 9:10:49 AM 9048991238 Wallace, Andrew BBF 5/25/2017 9:20:20 AM 3039875688 Tibby, Tim BBF 5/25/2017 9:20:30 AM 3039851238 Summer, Anne ABC 5/25/2017 9:25:15 AM 3039851234 Smith, John BBF 5/26/2017 9:01:49 AM 2126486878 Vasil, George BBF 5/26/2017 9:05:30 AM 2128514568 Wallace, Andrew BBF 5/26/2017 9:07:51 AM 2038689638 Summer, Anne BBF 5/26/2017 9:08:00 AM 2124486448 Smith, John BBF 5/26/2017 9:10:55 AM 6106488568 Murphy, Jessica ABC 5/26/2017 9:15:28 AM 2038689846 Smith, John Data Set 2 Agent Records- Client Start Date Start Time Service Rep Caller Name Call Reason (1) BBF 5/25/2017 9:10:56 Smith, John Kramer, Cosmo Account Balance BBF 5/25/2017 9:11:48 Wallace, Andrew Seinfield, Jerry Policy/Procedure BBF 5/25/2017 9:21:49 Tibby, Tim Costanza, George Redemption BBF 5/25/2017 9:22:52 Summer, Anne Benes, Elaine Redemption BBF 5/26/2017 9:02:40 Vasil, George Bing, Chandler Policy/Procedure BBF 5/26/2017 9:08:37 Summer, Anne Tribiani, Joe Account Balance BBF 5/26/2017 9:10:49 Smith, John Gellar, Monica Transaction Inquiry BBF 5/26/2017 9:13:08 Murphy, Jessica Green, Rachel Beneficiary Inquiry ABC 5/26/2017 9:16:01 Smith, John Chan, Jackie Account Balance Ideal Outcome- Client Start Date Start Time Calling Party Answering Agent Start Time Caller Name Call Reason (1) BBF 5/25/2017 9:10:20 AM 9707815688 Smith, John 9:10:56 AM Kramer, Cosmo Account Balance BBF 5/25/2017 9:10:49 AM 9048991238 Wallace, Andrew 9:11:48 AM Seinfield, Jerry Policy/Procedure BBF 5/25/2017 9:20:20 AM 3039875688 Tibby, Tim 9:21:49 AM Costanza, George Redemption BBF 5/25/2017 9:20:30 AM 3039851238 Summer, Anne 9:22:52 AM Benes, Elaine Redemption ABC 5/25/2017 9:25:15 AM 3039851234 Smith, John BBF 5/26/2017 9:01:49 AM 2126486878 Vasil, George 9:02:40 AM Bing, Chandler Policy/Procedure BBF 5/26/2017 9:05:30 AM 2128514568 Wallace, Andrew BBF 5/26/2017 9:07:51 AM 2038689638 Summer, Anne 9:08:37 AM Tribiani, Joe Account Balance BBF 5/26/2017 9:08:00 AM 2124486448 Smith, John 9:10:49 AM Gellar, Monica Transaction Inquiry BBF 5/26/2017 9:10:55 AM 6106488568 Murphy, Jessica 9:13:08 AM Green, Rachel Beneficiary Inquiry ABC 5/26/2017 9:15:28 AM 2038689846 Smith, John 9:16:01 AM Chan, Jackie Account Balance Call & Customer Sample Data v2.xlsx

Join / Merge two data sets based on multiple field criteria

AlexL

Question-

Is Alteryx able to join / merge two data sets based on multiple field criteria?

With one field criteria being relative/ range of time.

Problem -

Trying to find an automated way to associate Caller Name and Call reason to our telephone records.

Data set 1 is Telephone records

Data set 2 is Agent records

Fields in each data set are absolute in regards to Client, Start Date, Service Rep.

“Start Time” in Agent records can be 0-5min after the telephone record start time; therefore not an exact match

(I.e. – Telephone record has 9:01am and Agent record has 9:03am)

Data set 1 Telephone Records–

Client	Start Date	Start Time	Calling Party	Service Rep
BBF	5/25/2017	9:10:20 AM	9707815688	Smith, John
BBF	5/25/2017	9:10:49 AM	9048991238	Wallace, Andrew
BBF	5/25/2017	9:20:20 AM	3039875688	Tibby, Tim
BBF	5/25/2017	9:20:30 AM	3039851238	Summer, Anne
ABC	5/25/2017	9:25:15 AM	3039851234	Smith, John
BBF	5/26/2017	9:01:49 AM	2126486878	Vasil, George
BBF	5/26/2017	9:05:30 AM	2128514568	Wallace, Andrew
BBF	5/26/2017	9:07:51 AM	2038689638	Summer, Anne
BBF	5/26/2017	9:08:00 AM	2124486448	Smith, John
BBF	5/26/2017	9:10:55 AM	6106488568	Murphy, Jessica
ABC	5/26/2017	9:15:28 AM	2038689846	Smith, John

Data Set 2 Agent Records-

Client	Start Date	Start Time	Service Rep	Caller Name	Call Reason (1)
BBF	5/25/2017	9:10:56	Smith, John	Kramer, Cosmo	Account Balance
BBF	5/25/2017	9:11:48	Wallace, Andrew	Seinfield, Jerry	Policy/Procedure
BBF	5/25/2017	9:21:49	Tibby, Tim	Costanza, George	Redemption
BBF	5/25/2017	9:22:52	Summer, Anne	Benes, Elaine	Redemption
BBF	5/26/2017	9:02:40	Vasil, George	Bing, Chandler	Policy/Procedure
BBF	5/26/2017	9:08:37	Summer, Anne	Tribiani, Joe	Account Balance
BBF	5/26/2017	9:10:49	Smith, John	Gellar, Monica	Transaction Inquiry
BBF	5/26/2017	9:13:08	Murphy, Jessica	Green, Rachel	Beneficiary Inquiry
ABC	5/26/2017	9:16:01	Smith, John	Chan, Jackie	Account Balance

Ideal Outcome-

Client	Start Date	Start Time	Calling Party	Answering Agent	Start Time	Caller Name	Call Reason (1)
BBF	5/25/2017	9:10:20 AM	9707815688	Smith, John	9:10:56 AM	Kramer, Cosmo	Account Balance
BBF	5/25/2017	9:10:49 AM	9048991238	Wallace, Andrew	9:11:48 AM	Seinfield, Jerry	Policy/Procedure
BBF	5/25/2017	9:20:20 AM	3039875688	Tibby, Tim	9:21:49 AM	Costanza, George	Redemption
BBF	5/25/2017	9:20:30 AM	3039851238	Summer, Anne	9:22:52 AM	Benes, Elaine	Redemption
ABC	5/25/2017	9:25:15 AM	3039851234	Smith, John
BBF	5/26/2017	9:01:49 AM	2126486878	Vasil, George	9:02:40 AM	Bing, Chandler	Policy/Procedure
BBF	5/26/2017	9:05:30 AM	2128514568	Wallace, Andrew
BBF	5/26/2017	9:07:51 AM	2038689638	Summer, Anne	9:08:37 AM	Tribiani, Joe	Account Balance
BBF	5/26/2017	9:08:00 AM	2124486448	Smith, John	9:10:49 AM	Gellar, Monica	Transaction Inquiry
BBF	5/26/2017	9:10:55 AM	6106488568	Murphy, Jessica	9:13:08 AM	Green, Rachel	Beneficiary Inquiry
ABC	5/26/2017	9:15:28 AM	2038689846	Smith, John	9:16:01 AM	Chan, Jackie	Account Balance

Call & Customer Sample Data v2.xlsx

Common Use Cases

Accepted answers

jarrod

You can actually do this without a join. Instead, union the data and sort to find the "order" then use a multi-row tool to check that the dates are within a certain time frame and then mark the corresponding "pairs" that fit the criteria.

See the attached workflow for details as it's a little hard to explain in text.

Join with relative datetime.yxmd

All comments

AlexL

To clarify, I'm trying find a automated way to associate Caller name and Call Reason to the phone records.

Can you Trim out the seconds from Start Time, and just compare based on Hour and Minutes? Would that be enough precision for matching purposes if you had a new field "StartTimeMatch" to join on, leaving your actual Start Time data with hours:minutes:seconds intact?

david_fetters

Alex,

Building on GCs idea, I think rounding time to minutes into new fields in both data sets is your starting point. Then we just need to deal with the possible variation in time between sets. You're probably always going to run into problems with close cases (i.e. when an agent completes two phone calls within 5 minutes) so we just need to trap those cases and deal with them manually. Based on your sample data, that doesn't look like it's common for your agents to speak that quickly with two clients.

My solution would be to take your records in one table (consequence of that choice discussed later) and create five copies of each record, with each copy having its RoundedTime value increased/decreased by 1 minute. We'll say that you chose to create duplicates of your Table 2, Agent Records. If there was a record in that table with a Start Time at 9:22:35, you would have RoundedTime fields of 9:22, 9:21, 9:20, 9:19, 9:18. Do this by feeding the original record set into five formula tools that are set to subtract 0,1,2,3, or 4 minutes, and then union the results back together. Then you can join on Agent Name and RoundedTime fields in both tables. Because you have unique records in Data set 1, your join should produce 1:1 matches on the RoundedTime fields.

Based on your ideal outcome, I'd suggest doing the 'multiple copies' on Agent Records, because it's easier to deal with the unjoined records in Telephone Records. If you want to do it the other way, you may just have to unique unjoined records (and remember to increase your RoundedTime rather than decrease it.)

This is a pretty brutish approach, but I think it's a simple possible solution. I would be sure to add a unique ID to both tables, and then filter our duplicate records after your join. Duplicates indicate you've got some overlap between two Agent records and would be worth investigating. If you need an example of this, I can probably put one together this weekend (at Inspire right now!)

anthony

To me this seems like a join/sort. Since you will never have perfect data to join on time and since the time can vary wildly it might be a pain to put blocks of time around each call for a more definitive join.

I got your results with this join on rep/client/date and then sorting results by date/time/caller/rep etc. The edge case if a call is at 11:59pm and the report is next day, could be an issue depending on your hours.his:

AlexL

Thanks everyone for your responses!

Anthony, this is very close to what I need.

I added another Telephone record to the sample (11:15am) and the join forumula associates it to a 9am agent record.

I tried creating new field using the DateTimeDiff formula to create a formula to null out the agent records if the difference is greater than X.

Unfortunately, it does not like the excel time data and provides the errror "DATETIMEDIFF1: "09:10:20" is not a valid DateTime

Any thoughts?

Filter Join- Sample 1.jpg

Filter Join- Sample 2.jpg

Ideal Outcome (Revised to include 11:15am record)-

Client	Start Date	Start Time	Calling Party	Service Rep	Start Time	Caller Name	Call Reason (1)
BBF	5/25/2017	9:10:20 AM	9707815688	Smith, John	9:10:56 AM	Kramer, Cosmo	Account Balance
BBF	5/25/2017	9:10:49 AM	9048991238	Wallace, Andrew	9:11:48 AM	Seinfield, Jerry	Policy/Procedure
BBF	5/25/2017	9:20:20 AM	3039875688	Tibby, Tim	9:21:49 AM	Costanza, George	Redemption
BBF	5/25/2017	9:20:30 AM	3039851238	Summer, Anne	9:22:52 AM	Benes, Elaine	Redemption
ABC	5/25/2017	9:25:15 AM	3039851234	Smith, John
BBF	5/26/2017	9:01:49 AM	2126486878	Vasil, George	9:02:40 AM	Bing, Chandler	Policy/Procedure
BBF	5/26/2017	9:05:30 AM	2128514568	Wallace, Andrew
BBF	5/26/2017	9:07:51 AM	2038689638	Summer, Anne	9:08:37 AM	Tribiani, Joe	Account Balance
BBF	5/26/2017	9:08:00 AM	2124486448	Smith, John	9:10:49 AM	Gellar, Monica	Transaction Inquiry
BBF	5/26/2017	9:10:55 AM	6106488568	Murphy, Jessica	9:13:08 AM	Green, Rachel	Beneficiary Inquiry
ABC	5/26/2017	9:15:28 AM	2038689846	Smith, John	9:16:01 AM	Chan, Jackie	Account Balance
BBF	5/26/2017	11:15:45 AM	2038688657	Summer, Anne

Call & Customer Sample Data v2.xlsx

Telephone - Agent Records Data join.yxmd

jarrod

See the attached workflow for details as it's a little hard to explain in text.

Join with relative datetime.yxmd

AlexL

Hi Jarrod,

This is great! I will need to study it a few times to fully absorb.

Thanks!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703