This problem is something I covered when I spoke with my colleagues at Alteryx Inspire London 2018 and is available if you wish to listen to that talk Link. In this blog I thought I would cover in more detail the solution to resolve this problem.
The problem sounds simple but in fact is quite an interesting and complicated problem when thought of in more detail. Products that should have been delivered to a number of stores that are in demand for those products has mistakenly been misdelivered to stores that don’t require that product. Is there a way to work out a way to collect the products from those stores that don’t require them and deliver them to the nearest store? As the stores only stock a limited amount of these products you are only able to collect from those stores, which have those products once and deliver it to only one store which is in demand for those products.
High level solution
The way to approach this solution is to calculate the distance between all of your Demand Stores (Stores that need the product) and your Supply Stores (Stores with the product) for every possible store combinations/scenario. Once you have a list of every store combinations and distance you need to loop through a process of selecting the closest distance for one combination of stores and then removing that Demand store and Supply store from the list of all other possible combinations, as you have used stores and can’t use a store more than once for your solution.
I’m going to show how to solve this problem using Alteryx for my data I used a random sample of Geolytix Open Retail Points to get real world locations and created a pseudo Fascia called Guisseppe. To produce 100 Demand store locations and 120 Supply store locations. The data for Demand and Supply are identical and have RecordID to distinguish the stores from each other.
The first step in Alteryx is to use the Create Points Tools for both datasets and then use the Append Fields tools. This creates every possible combination/scenario of Demand and Supply stores with our data.
The second step after the Append Fields tool is the Distance tool which can give the distance between two points. In our case would be the Centroid Demand and Centroid Supply fields and using straight line distance in miles.
The following steps are data preparation where I sort by distance getting the nearest distance, to furthest distance and I remove the two centroid fields. As I no longer need them and keeping them in the workflow will affect performance.
The last step is to create the looping processing of selecting the closest distance for a combination of stores, e.g. Demand store 1090 and Supply store 1536 with a distance of 0.27 miles and then removing those stores from the list of other potential combinations and then repeating the process. This is done in Alteryx using an Iterative Macro which is a macro which will run through every record and then loop the records back through the workflow, repeating the entire process as many times as is specified, or until a condition is met.
Looking inside the Iterative macro the Sample tool takes 1 record and then immediately outputs the record this will always be the nearest distance due to sorting by distance before the macro. From the Sample Tool we then use a combination of Join tools to match up the Demand and Supply Store Record ID to the list of the combinations. We then only use the Right Join of both tools as this is the remaining data minus the Demand and supply store from the Sample tool. What we are left with are then remaining possible combinations of stores and it’s this data we want outputted to the L output, which for this Iterative Macro is the iteration output that we want this macro to loop through to find a Supply store for every Demand Store.
In the Interface Designer for this macro it’s important we select which Macro Output is the Iteration Output and also select the maximum number of iterations and then what we want to happen, when the maximum number if iterations are hit.
Looking at the O output from the macro we can see 100 records have outputted. This means we have the nearest distance to a Supply store that could possibly provide the missing products for every Demand store. Also we not used a store more than once, thus avoiding the situation of going to a supply store and finding the products having already being collected, or delivering the products to a Demand store and finding they already had those products delivered.