Brainstorming "Find Nearest" Data Prep for Tableau Map

Question

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 ROMNearest Comp ANearest Comp BNearest Comp CNearest Comp DCustA_001CustC_005N/ACustB_011CustC_005CustD_002CustB_003CustD_007CustA_031N/ACustC_042CustD_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 CompComp Str #CustA_001ROMCustC_005CustA_001CustBCustB_011CustA_001CustCCustC_005CustA_001CustDCustD_002CustB_003ROMCustD_007CustB_003CustACustA_031CustB_003CustACustA_031CustB_003CustCCustC_042CustB_003CustDCustD_007

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

Nearest Competitor Anon.yxzp

dataMack · Accepted Answer

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.