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

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