7 Replies Latest reply on Jan 8, 2020 9:03 AM by Dan Cory

    Attributing County .xlsx Data to County .shp File (Non-Spatial to Spatial)

    Clint Ratliff

      My goal is to build out a map of the U.S. that highlights the Top 500 healthiest counties based on a series of ranking criteria. I have a spatial file (.shp) that delivers county boundings via polygons. I have a non-spatial .xlsx that has a selection of the corresponding counties and rankings based on a number of factors in regards to health. Furthermore, I have a third file with spatial data to layer in the the healthiest cities in the U.S., but we can put that on the back burner for the moment.

       

      My thought was, if I have a key I can use from each file, I should be able to join the data successfully. For this reason, I decided to use the County FIPS codes to cross reference between the two files as my .shp file already had them listed and a little match/index in excel could help me link my health rankings sheet. I tried this; it did not work. The first error that I received was that the type of each County FIPS variable did not match (string versus whole #). I changed each to whole; I received the same error message. I changed each to string; the join worked, but all of the data disappeared.

       

      In digging through Tableau forums, I came across this often-referenced post:  Dual Axis Mapping - Many Ways  For joining .shp and .xlsx, this post recommended a full outer join on calculated fields using "0" and "1" to keep all rows of data and sidestep the incompatibility between the two files. I did this, my data was stored and accessible, but it wasn't "truly" linked. For instance, I loaded in my geometry and tried to filter by county based on the top rank from my .xlsx. The result highlighted 50 counties across the U.S., but they came in alphabetically based on the list contained in my .shp file rather than the rankings from my .xlsx.

       

      I made a workaround here to show what I was looking to do. Through concatenating county & state fields, I was able to specify the Top 500 counties in a custom filter. However, as this was a user-generated list, it's worthless at manipulating because it lacks the functionality of a real model. I have attached this image for reference along with my workbook. The county data in my spreadsheet corresponds to the county data in my .shp file, but I just can't make them connect. Help?

       

       

      Top 500 Healthiest Counties.png