calc distance between all possible combinations
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
I want to know if the following can be done on Alteryx, if yes, how?
I have 2 lists of locations in Excel (I have their latitudes and longitudes). I want to calculate the distance between all possible combinations that can be made between list 1 and 2. and then I want to know which of the locations in list 2 are closest to locations in list 1.
I know this can be done using the haversine formula in excel, but my list is too large for excel to handle.
Solved! Go to Solution.
- Labels:
- Datasets
- Google Analytics
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey Garad,
I believe what you need is a "Cortesian Product" or Combination of your locations - everything to everything. Simple 3 steps:
1. Add a dummy column with a Formula tool say "JoinBy" = 1 to both tables
2. Use Join tool and use the JoinBy column as merger
3. Use the Distance tool now!
Let me know if you need more assistance.
Best,
Oly
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Here is what I recommend
1. Use the Create Points tool to turn your lat/longs into spatial objects
2. Use the Append Fields tool to attach every record in set 1 with every record in set 2
3. Use the Distance tool to calculate the distance between each pair of points
4. Sort your data by distance
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Correct, Append in this case will work same as join on all!
