Hello,
I'm trying to create a worksheet in tableau, built from data I have in an excel spreadsheet.
My excel spreadsheet consists of multiple columns, two of which are 'Case ID' and 'States Affected'. Some of the records in the 'States Affected' column consist of multiple state names separated by commas - where more than one state has been affected for a given case ID - e.g. California, Alaska, Texas. (Please refer to the below image).
I'd like to do some analysis in tableau that shows how many times a state has been affected, ideally using a map but the records with multiple values separated by columns show up as unrecognised locations. (Please refer to the below image).
Any advice on how to rectify this, either directly in tableau or within my spreadsheet in excel would be greatly appreciated.
The data needs to be both SPLIT and reshaped. You could do this in Tableau Prep. More difficult to do in Tableau Desktop as it is not designed to do both (one or the other, but not both). If you want to try and follow the Flerlage Twins method for doing both here's the link: Split & Pivot Comma-Separated Values Again, would do this in Tableau Prep which is available to you as part of your existing licensing; same license key for Desktop is applied at time of registration.
As an aside, the verbiage Nationwide is meaningless to Tableau, so some other effort would be needed to recognize or rather change that value to something that matches Tableau's underlying geo-database. Likely it would be populated into a different column such as Country as it's not a State/Province value that would be recognized and match upon.
Best, Don
(Please, don't forget to click Select as Best or Upvote !)
