How to join based on closest date

Hi all!

I'm having trouble joining my two datasets based on some date logic. I have one dataset with survey response data and another with visitation data. They both have the same unique respondent ID fields, however there are multiple visits and only one response date. I need to match the response data to the visit that happened directly prior to that date. In other words, I need to join the second dataset by respondent ID and the date that is closest (but before) the date in the first dataset.

Based on the similar post "How to Join on a Date Range" I thought maybe I could generate dates for the 7 days prior to the response rate and then do a standard join? I'm not sure how to generate these rows.

Thanks in advance for the help!!

Lauren

Date Time

Join

Preparation

Accepted answers

BCarley

Lauren,

Attached is a workflow that should be appropriate, I've noticed that some times you have two visits on the same day and we don't have logic for picking which visit the response is for. How do you want to treat that?

Ben - Keyrus UK

visitation_example.yxzp

All comments

BCarley

Hi Lauren,

Would you be able to post some example data and I would be happy to put together a workflow for you.

Ben Carley - Keyrus UK

l_blumberger

For sure!

Attached is a sample of each dataset.

Response Info.xlsx

Visit Data.xlsx

BCarley

Lauren,

I am having trouble accessing the gallery to upload an example workflow but I have approached the task in a slightly different way. I have joined the two data sources on client ID with no other field. This means you will match every item to every other item and end up duplicating your data.

Once you have done this you will be able to filter all the results where the response data is before your visitation date (as I am assuming that you do not want responses on visits that haven't happened)

Once you have filtered out the invalid rows you want to pick just one of the rows for each pair, so sort by customer ID, Response Date, Visitation Date and the use the sample tool to pick the first row as grouped by Visitation ID and Customer ID.

By using the combination of Sort and Sample you should pick only the most recent response for each visitation. Does that make sense or am I misunderstanding your data? (or making a mistake!)

-Ben

workflow example.PNG

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335