Hello All,
Sadly I am unable to share data, but I hope my example gives enough detail.
I have the following data:
- 24000 different customers, each with an exact Lat/Long. I have given each of these a unique recordID and create at Spatial Point for each
- 2280 different stores, each with an exact Lat/Long and I have created a spatial point for each.
I have then appended the Customer with the stores and been able to fine the distance for each customer to the nearest stores (Using same logic as https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Distance-between-two-sets-of-Lat-Longs/td-p/139233) This gives me some 50.8M combos!. The data looks something like this:
| Record ID (Customer) | Store ID | Distance in Mile |
| 1 | 1 | 50.2123123 |
| 1 | 2 | 1.3127363 |
| 1 | 3 | 13.3191057 |
What I need to do is somehow get the shortest distance and Store ID for each customer (Record ID). I have tried using the summarise tool to find the shortest distance for each customer (Summarised as group by Record ID, min(Distance)) which worked, but I don't know how to link that back to the customer and keep the store ID?
I have tried matching joining by Record ID and distance in mileage to the summarised minimum distance in mileage but it failed to correct match
Any ideas? Thank you
