5 Replies Latest reply on Aug 8, 2014 10:18 AM by allan.walker.0

    Bug with mapping variations of state name

    Aaron Anzalone

      Setup:

       

      State/ProvinceValues
      Texas5
      TX2
      Louisiana10
      LA2

       

       

      When data like the above exists, Tableau only uses 1 variation of the state, even though both "Texas" and "TX" are mapped to Texas. For example, placing SUM(Values) on the Label Shelf will show 2 for Texas, and 10 for Louisiana instead of 7 for Texas and 12 for Louisiana. I've attached a workbook that shows what I'm talking about.

        • 1. Re: Bug with mapping variations of state name
          Tim Lens

          hi Aaron,


          It actually recognized both, but placed them on top of each other on the map. (so you only see 1 value)
          You could group them together, and that will take care of it, see workbook attached.

          1 of 1 people found this helpful
          • 2. Re: Bug with mapping variations of state name
            Aaron Anzalone

            Hi Tim,

             

            You are correct, the marks are stacked on top of each other. I stand by my point though. The "Edit Locations" window gives the user the appearance that Tableau has recognized the locations as the same, but recognition is not carried over to other parts of the application. I also agree with you that groups represent an option, however my experience has been that there is a rather large performance penalty when using groups.

             

            Just to give you some background, most of my work with Tableau revolves visualizing submitted sales reports from about 300 different vendors. As you can imagine, 300 vendors means 300 different formats and I appreciate Tableau because it makes working with this data a breeze. I was caught off-guard when my Sales VP said my "Sales by State" report showed incorrect sum(sales).

             

            Thanks,

            Aaron

            • 3. Re: Bug with mapping variations of state name
              Neil Miller

              I am having this problem as well.

               

              Has this been fixed yet?

              • 4. Re: Bug with mapping variations of state name
                Aaron Anzalone

                Hi Neil,

                 

                No, this problem still exists. I don't know what your use case is, but I ended up using Alteryx to tranform the data and then saving a TDE and using that as my source. Less than ideal, I admit. My first attempt was to use a scalar-value function in SQL and using that to do convert the data that way. I've since expanded my Alteryx workflow to fix some other problems so I stopped using the SQL function. My function is below:

                 

                CREATE FUNCTION [dbo].[abbrState]

                (@State varchar(50))

                RETURNS varchar(50)

                AS

                BEGIN

                declare @Return varchar(2)

                select @return = case @State

                when 'Alabama (AL)' then 'AL'

                when 'Alabama' then 'AL'

                when '1' then 'AL'

                 

                 

                when 'Alaska (AK)' then 'AK'

                when 'Alaska' then 'AK'

                when '2' then 'AK'

                 

                 

                when 'Arizona (AZ)' then 'AZ'

                when 'Arizona' then 'AZ'

                when '3' then 'AZ'

                 

                 

                when 'Arkansas (AR)' then 'AR'

                when 'Arkansas' then 'AR'

                when '4' then 'AR'

                 

                 

                when 'California (CA)' then 'CA'

                when 'California' then 'CA'

                when '5' then 'CA'

                 

                 

                when 'Colorado (CO)' then 'CO'

                when 'Colorado' then 'CO'

                when '6' then 'CO'

                 

                 

                when 'Connecticut (CT)' then 'CT'

                when 'Connecticut' then 'CT'

                when '7' then 'CT'

                 

                 

                when 'Delaware (DE)' then 'DE'

                when 'Delaware' then 'DE'

                when '8' then 'DE'

                 

                 

                when 'District of Columbia (DC)' then 'DC'

                when 'District of Columbia' then 'DC'

                when '9' then 'DC'

                 

                 

                when 'Florida (FL)' then 'FL'

                when 'Florida' then 'FL'

                when '10' then 'FL'

                 

                 

                when 'Georgia (GA)' then 'GA'

                when 'Georgia' then 'GA'

                when '11' then 'GA'

                 

                 

                when 'Hawaii (HI)' then 'HI'

                when 'Hawaii' then 'HI'

                when '12' then 'HI'

                 

                 

                when 'Idaho (ID)' then 'ID'

                when 'Idaho' then 'ID'

                when '13' then 'ID'

                 

                 

                when 'Illinois (IL)' then 'IL'

                when 'Illinois' then 'IL'

                when '14' then 'IL'

                 

                 

                when 'Indiana (IN)' then 'IN'

                when 'Indiana' then 'IN'

                when '15' then 'IN'

                 

                 

                when 'Iowa (IA)' then 'IA'

                when 'Iowa' then 'IA'

                when '16' then 'IA'

                 

                 

                when 'Kansas (KS)' then 'KS'

                when 'Kansas' then 'KS'

                when '17' then 'KS'

                 

                 

                when 'Kentucky (KY)' then 'KY'

                when 'Kentucky' then 'KY'

                when '18' then 'KY'

                 

                 

                when 'Louisiana (LA)' then 'LA'

                when 'Louisiana' then 'LA'

                when '19' then 'LA'

                 

                 

                when 'Maine (ME)' then 'ME'

                when 'Maine' then 'ME'

                when '20' then 'ME'

                 

                 

                when 'Maryland (MD)' then 'MD'

                when 'Maryland' then 'MD'

                when '21' then 'MD'

                 

                 

                when 'Massachusetts (MA)' then 'MA'

                when 'Massachusetts' then 'MA'

                when '22' then 'MA'

                 

                 

                when 'Michigan (MI)' then 'MI'

                when 'Michigan' then 'MI'

                when '23' then 'MI'

                 

                 

                when 'Minnesota (MN)' then 'MN'

                when 'Minnesota' then 'MN'

                when '24' then 'MN'

                 

                 

                when 'Mississippi (MS)' then 'MS'

                when 'Mississippi' then 'MS'

                when '25' then 'MS'

                 

                 

                when 'Missouri (MO)' then 'MO'

                when 'Missouri' then 'MO'

                when '26' then 'MO'

                 

                 

                when 'Montana (MT)' then 'MT'

                when 'Montana' then 'MT'

                when '27' then 'MT'

                 

                 

                when 'Nebraska (NE)' then 'NE'

                when 'Nebraska' then 'NE'

                when '28' then 'NE'

                 

                 

                when 'Nevada (NV)' then 'NV'

                when 'Nevada' then 'NV'

                when '29' then 'NV'

                 

                 

                when 'New Hampshire (NH)' then 'NH'

                when 'New Hampshire' then 'NH'

                when '30' then 'NH'

                 

                 

                when 'New Jersey (NJ)' then 'NJ'

                when 'New Jersey' then 'NJ'

                when '31' then 'NJ'

                 

                 

                when 'New Mexico (NM)' then 'NM'

                when 'New Mexico' then 'NM'

                when '32' then 'NM'

                 

                 

                when 'New York (NY)' then 'NY'

                when 'New York' then 'NY'

                when '33' then 'NY'

                 

                 

                when 'North Carolina (NC)' then 'NC'

                when 'North Carolina' then 'NC'

                when '34' then 'NC'

                 

                 

                when 'North Dakota (ND)' then 'ND'

                when 'North Dakota' then 'ND'

                when '35' then 'ND'

                 

                 

                when 'Ohio (OH)' then 'OH'

                when 'Ohio' then 'OH'

                when '36' then 'OH'

                 

                 

                when 'Oklahoma (OK)' then 'OK'

                when 'Oklahoma' then 'OK'

                when '37' then 'OK'

                 

                 

                when 'Oregon (OR)' then 'OR'

                when 'Oregon' then 'OR'

                when '38' then 'OR'

                 

                 

                when 'Pennsylvania (PA)' then 'PA'

                when 'Pennsylvania' then 'PA'

                when '39' then 'PA'

                 

                 

                when 'Puerto Rico (PR)' then 'PR'

                when 'Puerto Rico' then 'PR'

                when '40' then 'PR'

                 

                 

                when 'Rhode Island (RI)' then 'RI'

                when 'Rhode Island' then 'RI'

                when '41' then 'RI'

                 

                 

                when 'South Carolina (SC)' then 'SC'

                when 'South Carolina' then 'SC'

                when '42' then 'SC'

                 

                 

                when 'South Dakota (SD)' then 'SD'

                when 'South Dakota' then 'SD'

                when '43' then 'SD'

                 

                 

                when 'Tennessee (TN)' then 'TN'

                when 'Tennessee' then 'TN'

                when '44' then 'TN'

                 

                 

                when 'Texas (TX)' then 'TX'

                when 'Texas' then 'TX'

                when '45' then 'TX'

                 

                 

                when 'Utah (UT)' then 'UT'

                when 'Utah' then 'UT'

                when '46' then 'UT'

                 

                 

                when 'Vermont (VT)' then 'VT'

                when 'Vermont' then 'VT'

                when '47' then 'VT'

                 

                 

                when 'Virginia (VA)' then 'VA'

                when 'Virginia' then 'VA'

                when '48' then 'VA'

                 

                 

                when 'Washington (WA)' then 'WA'

                when 'Washington' then 'WA'

                when '49' then 'WA'

                 

                 

                when 'West Virginia (WV)' then 'WV'

                when 'West Virginia' then 'WV'

                when '50' then 'WV'

                 

                 

                when 'Wisconsin (WI)' then 'WI'

                when 'Wisconsin' then 'WI'

                when '51' then 'WI'

                 

                 

                when 'Wyoming (WY)' then 'WY'

                when 'Wyoming' then 'WY'

                when '52' then 'WY'

                 

                 

                when 'Ontario (ON)' then 'ON'

                when 'Ontario' then 'ON'

                when '53' then 'ON'

                 

                 

                when 'Quebec (QC)' then 'QC'

                when 'Quebec' then 'QC'

                when '54' then 'QC'

                 

                 

                when 'Nova Scotia (NS)' then 'NS'

                when 'Nova Scotia' then 'NS'

                when '55' then 'NS'

                 

                 

                when 'New Brunswick (NB)' then 'NB'

                when 'New Brunswick' then 'NB'

                when '56' then 'NB'

                 

                 

                when 'Manitoba (MB)' then 'MB'

                when 'Manitoba' then 'MB'

                when '57' then 'MB'

                 

                 

                when 'British Columbia (BC)' then 'BC'

                when 'British Columbia' then 'BC'

                when '58' then 'BC'

                 

                 

                when 'Prince Edward Island (PE)' then 'PE'

                when 'Prince Edward Island (PE)' then 'PE'

                when '59' then 'PE'

                 

                 

                when 'Saskatchewan (SK)' then 'SK'

                when 'Saskatchewan' then 'SK'

                when '60' then 'SK'

                 

                 

                when 'Alberta (AB)' then 'AB'

                when 'Alberta' then 'AB'

                when '61' then 'AB'

                 

                 

                when 'Newfoundland and Labrador (NL)' then 'NL'

                when 'Newfoundland and Labrador' then 'NL'

                when '62' then 'NL'

                 

                 

                else @State

                end

                 

                 

                return @return

                end

                • 5. Re: Bug with mapping variations of state name
                  allan.walker.0

                  Hi Aaron,

                   

                  This workbook might help.  You can then do a dual axis with text that will display the sum over the circle mark.

                   

                  Best Regards,

                   

                  Allan