Brainstorming "Find Nearest" Data Prep for Tableau Map

[cbridges]

This is a two-fold question...

First, I'm brand new to spatial tools so I'm wondering if there's a cleaner/easier way to accomplish what I have in the attached workflow.

The gist is, for each of my customer stores, what is the nearest competitor in the whole market (ROM) and what is the nearest store for each other competitor.

So I end up with something like this (completely made up, but practical data is in workflow):

Str #	Nearest ROM	Nearest Comp A	Nearest Comp B	Nearest Comp C	Nearest Comp D
CustA_001	CustC_005	N/A	CustB_011	CustC_005	CustD_002
CustB_003	CustD_007	CustA_031	N/A	CustC_042	CustD_007

But given that I'm comparing every single store to every other single store five times, it gets messy...

Second, I'm trying to think of a way to structure the output so that I can plot these relationships on a Tableau map. I'd like to be able to choose a customer and/or a region, and then also plot the nearest ROM, Comp A, Comp B, etc. I'm thinking of a filter so I can select any combination of the competitors. Do I need to stack my results so that the data is "long" - more like this?

Str #	Nearest Comp	Comp Str #
CustA_001	ROM	CustC_005
CustA_001	CustB	CustB_011
CustA_001	CustC	CustC_005
CustA_001	CustD	CustD_002
CustB_003	ROM	CustD_007
CustB_003	CustA	CustA_031
CustB_003	CustA	CustA_031
CustB_003	CustC	CustC_042
CustB_003	CustD	CustD_007

Can't quite wrap my head around the best way to prep the data...

[dataMack · Accepted solution]

Yes- your approach of thinking 'more rows' and the structure you are thinking of where your filter will either be 'ROM' or a specific competitor to return that result for a given location of yours will likely be the best structure for Tableau.

If you have nearest overall, then 4 specific competitors, I might use a filter off a single input and send each to the respective spatial match, then union them back together in the layout (long) you suggested.  That shouldn't be too messy.

[cbridges]

I think your explanation of the workflow structure is what I did. It resulted in an exponential number of find nearest (each customer X overall, then each customer x every other customer). It's too bad that Find Nearest doesn't have a "Group By" feature. If it did, I would only need 2 tools per customer.

I'll make my data "long" and see if Tableau likes it better - thanks!