Standardize values in a "Region" field

[Anonymous]

Hi All,

I have a Region field in my Eloqua data base with around 302 values. However the way it is stored is

There are a values with upper case and lower case eg: UNITED STATES, United States and unites states. Similarly for VIETNAM and Vietnam  would like to have a standardized format for these values.

How can this be achieved?

[vishsaggi]

May be try this:

LOAD Upper(Region) AS Region inline [

Region

United States

UNITED STATES

United KINGDOM

Virgin Islands, British

Virgin ISLANDS, U.s.

Virgin ISLANDS, U.s.

UZBEKISTAN

Uzbekistan

];

Then use Region as Listbox, it would display distinct Regions. If your requirement is different please elaborate a little.

[Anonymous]

Hi Vishwarath,

Thank you for your response. the use of inline is  manual entry in Load script. However If a user enters a new region I will have to update this manually which I am not looking to have. Is there a different method where I don't have to update the field manually.

[Anil_Babu_Samineni]

When you talk about Manually, You can do this? Check as per your need. I am sure Viswanath solutions works if not you would provide output also if you know

LOAD If(SubStringCount(Region, 'United States'), 'UNITED STATES',If(SubStringCount(Region, 'Vietnam'), 'VIETNAM',Region)) AS Region, Capitalize(Region) as Region1 inline [

Region

United States

UNITED STATES

United KINGDOM

Virgin Islands, British

Virgin ISLANDS, U.s.

Virgin ISLANDS, U.s.

UZBEKISTAN

Uzbekistan

VIETNAM

Vietnam

];

[vishsaggi]

I just showed you an example using Inline. In your actual load script just use like below where you have the Region field coming. Like below:

LOAD col1,

           col2,

           ....,

           Upper(Region) AS Region,

          Col4....

FROM yourdatasourcename;

[#6]

Hi Ajinkya

    I think he means you should input 'Upper(Region)  as Region '  when you load the source data. Therefore  all the  values of  'Region'  will in Uppercase.

[Anonymous]

Great got it Bob!. Thank you so much. It worked. All I had to do is use UPPER(Region) as Region.

One other question I have is there are some values like below which needs to be renamed. How should I rename it and as well as keep the Capitalization intact

IN

INDIA

UNITED STATES MINOR OUTLYING ISLAND

UNITED STATES MINOR OUTLYING ISLANDS

[PrashantSangle]

Hi,

If possible maintain a mapper table which have this combination.

Regards,

Prashant

[#9]

Hi Ajinkya

    I think it's good to make a mapping table.

    Mapping table:

    load * inline

    [ Region,Region2

      US,US

      IN,INDIA

UNITED STATES MINOR OUTLYING ISLAND,UNITED STATES MINOR OUTLYING ISLANDS ] ;

    Then ' Region2' will be  the dimension you want .

Regards

Bob

[Peter_Cammaert]

Note that if your source system grows and doesn't impose limitations on the allowed Region field values, it's close to impossible to create a solution that handles all possible anomalies now and in the future. There are too many possibilities (a single missing punctuation mark will create a new entry)

You will end up with manual work (either in script code, in an include file, in a specific translation/mapping table in a DB or in an Excel) in any case.