I would use an inline table or an Excel file with all distinct values of your countries and the wanted outcome
define it as mappping table and convert your Countryfield according
short example with inline
mapping load * Inline [
in your fact table:
applymap('CountryConvert', Country) as NewCountryShort,
If you have a lot of distinct values it is an awesome one time job
Use Mapping load
mapping LOAD * Inline
LOAD ApplyMap('MAP',Country,'NA') as Country,Count
test.qvw 146.0 K
I see you've already asked this question, and received some decent answers.
Would suggest you show some courtesy to those who have already answered your question by clarifying why their answers don't meet your requirements.
You can always try to use function match() and define all of the possible versions of names for each country. You will have to nest IF statements in your load script, if you want to re-map more than one country. If no match has been found, word MISSING will be displayed (you can of course change it to anything you like):
IF(match(Country, 'US', 'United States', 'America' , 'U.S.A', 'USA', 'AMERICA') , 'USA',
IF(match(Country, 'CHN', 'China'), 'CHINA',
IF(match(Country, 'IND', 'India'), 'INDIA', 'MISSING'
) AS CountrySame
But of course - solutions with mapping loads will work for this task too.
match.qvw 144.8 K