This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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?
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!)
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:
This is a nice little macro to convert various time formats to iso time format. I used this on a 100M row table and it took a long time so best to clean up large tables with other methods. But for millions of rows it works great.
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.