2 of 2 people found this helpful
Hello - I think you have to first find the distance between two points. For that use the following KB article
and then use a min function of distance and place that in the filter. Please help me by providing a sample data set.
I played with that formula and others but not getting the desired result.
Attached is a sample set of the data I'm using. It has ten customers and ten stores. Trying to determine which store is closest to each customer. In other examples on the net I see that people are setting up a parameter and searching one store at a time. I want to have Tableau output a whole list rather than going one by one as my actual data set has thousands of locations.
Locations sample.xlsx 9.4 KB
Hello David - If you don't like to show all your Stores and Customers on Map, but only want the Shortest Output then that also can be easily done. Here in the example shows for every Customer the Close 3 nearby store and the distance. Hope this will help. Here I have changed the Source Query to the following:
SELECT [Sheet1$].[Latitude] AS [Latitude],
[Sheet1$].[Longitude] AS [Longitude],
[Sheet1$].[Loc Name] AS [Store Name],
[Sheet1$].[Loc Type] AS [Store],
[Sheet1b$].[Latitude] AS [Latitude1],
[Sheet1b$].[Loc Name] AS [Customer Name],
[Sheet1b$].[Loc Type] AS [Customer],
[Sheet1b$].[Longitude] AS [Longitude1]
FROM [Sheet1$] INNER JOIN [Sheet1$]
[Sheet1b$] ON ([Sheet1$].[Loc Name] <> [Sheet1b$].[Loc Name])
where [Sheet1$].[Loc Type] = 'Store'
AND [Sheet1b$].[Loc Type] = 'Customer'
minimum distance.twbx 69.2 KB
That worked. Thanks for the help.
This works great for me when i am using a list of 100 stores and 100 customers. I am getting a Query to large issue when comparing a large amount of data. Say 2,500 stores to 100,000 or more customers. Is there any other way to optimize the query? Thanks for the help!
"Database error 0x80004005: The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage space on the disk to store the query result."