Using Alteryx to find the nearest alternative

This week at Data School we’ve been building a new viz each day from a different data source.  The topics varied from weapons confiscated at airports to the British National Food survey. My personal favourite was visualising farmer’s markets in the US.  We were given free reign to come up with our own question to ask of the data, so I asked “Where do I go if my local farmer’s market closes?”

Here’s a sample of the data, once I’d already transposed the food items into rows (rather than several columns) using Alteryx.

The data contained 30 different products that a farmers market could sell.  I didn’t just want to visualise where the nearest market but specifically where the nearest market was that sold specific products that had previously been available at your local market. In Alteryx I created points from the longitude and latitude fields then filtered by the products as you can see in the workflow below.  This meant I had a list for all the farmers markets along with their spatial points for each possible product.

Creating points then filtering by product

The next step was to use the find nearest tool to find the nearest market that sold a particular product.  In this case the target and universe inputs are linked to the same data.

The nearest market to a market is itself, so it is vital to tick ‘ignore 0 distance matches’.

Once I had repeated this action for each product I unioned the data together.   Further reshaping was required to get the home stores and nearest alternative stores on different rows as the find nearest tool creates new columns for the nearest rather than new rows. I also used the summarise tool to concatenate together items that were available at the same market.  Here is what the viz looked like in Tableau:

With British supermarkets currently having standoffs with some of their suppliers, perhaps finding the nearest alternative is something that could be useful to a lot of consumers.