Find Nearest (based on date criteria)

[bertal34]

Table 1 - Customer purchase data (past 3 years), includes customer id, sale date, latitude, longitude

Table 2 - Retail store data, includes store number, status (open/closed), open date, close date, latitude, longitude

Customer purchase data is for ecommerce, so there is no association with a Brick & Mortar retail store.  For each customer purchase record, I need to find the nearest retail store (within 30 miles) that was open on the sale date.  When a store closes, the status changes to "closed" and close date populates.  When open, the close date is null.

[ArnavS]

The first thing I would do is data prep to filter the stores that were open during the sale date. After you have the relevant stores, use the Find Nearest tool to find the nearest store. You are able to set a max distance with this tool (if 30 miles is your limit).

If you are able to provide data, I may be able to be of more assistance. Hope this helps!

[bertal34]

@ArnavS  I don't think it's that simple.  I have a 3 year period of Sales, during which time a given store may open or close.  If store does close, it is closed for good.  One thing I forgot to mention, the purchase history includes ECOM sales, which have no association with a Brick & Mortar store.

I have attached an example of the 2 tables and the expected outcome.

I appreciate you trying to help!

[ArnavS · Accepted solution]

@bertal34

I created a workflow with a high level breakdown of what you are trying to accomplish. I filtered the sale date with the closed dates to remove any closed stores then calculated the distance from the customers to open stores. 

Hope this helps!

[bertal34]

@ArnavS  Sorry it took me couple days to get back to you.  Thank you for your solution it worked great!  Appreciate your help so quickly.