Standardise Country 🌎

Danny Roden
Danny Roden Administrator
edited December 2023 in Functions library

Standardise Country

image.png

The function uses reference data (contained within the .dmxd file) to standardise known country aliases to the standard form (e.g. England = United Kingdom).


See below for a preview of the logical definition of the function and a preview of the dataset that it comes with.

image.png


image.png

If, after processing your data, you identify there are quite a few records getting the 'Unknown Country' error message, you may want to add more aliases to this list in the lookup data.

You can do this by downloading the reference data from Data Studio (using the 'download as csv' button) and edit it accordingly.

Once you've made those changes, upload it into the existing dataset (using the 'Upload new data' button).

After you've done this the function will dynamically update to point at referencing the updated data.



Compatibility:

This function is compatible with all instances of Data Studio from v2.1.11 onwards.

[dl-button|Download|https://us.v-cdn.net/6031645/uploads/ZGCUF8SYYIUP/standardise-country.dmxd]

Tagged:

Comments

  • Henry Simms
    Henry Simms Administrator

    I’ve been using this function recently, and have extended it to optionally match a country to its 3-letter country code, rather than just a standardized name.

    To do that, I've added an extra "Country code" column to the reference data domain:

    image.png


    The reference data uses the ISO 3166-1 alpha-3 standard to define country code.

    And I've added a new function to return the code:

    image.png

    While making those changes I also made a few improvements including:

    1. Added about 600 new country name aliases (from 928 to 1564)
    2. Corrected some issues in the original domain, eg to differentiate Congo / COG from Democratic Republic of the Congo / COD, rename Macedonia to North Macedonia, added South Sudan (SSD)
    Standardize country name 3 letter code.dmxd

    Compatibility:

    This function is only compatible with all instances of Data Studio from v2.4.3 onwards.

  • Henry Simms
    Henry Simms Administrator

    I've updated the country reference data domain to add some additional country aliases. This latest version of the "Standardize Country Name" function package is compatible with Data Studio v2.4.6 onwards.

    Standardize country name 3 letter code.dmxd


  • Dan Mason
    Dan Mason Experian Contributor
    edited April 2022
    Standardise country name and get 2-letter and 3-letter country codes.dmxd

    I've updated the country reference data to add an additional 'Country code (2 letter)' column, to add some additional country aliases and to update a few 3-letter country codes.

    reference data.png

    I've also added a new function to return the 2-letter code

    image.png

    Compatibility:

    This function is only compatible with all instances of Data Studio from v2.6.3 onwards.

  • Simon_
    Simon_ Member

    I've found that the Aperture Data Studio phone validation returns "Macao SAR China" as the Phone Country, so it might be worthwhile to include that as an alias in the lookup table.

  • Dan Mason
    Dan Mason Experian Contributor
    edited August 2022
    Standardize country name and get 2-letter and 3-letter country codes.dmxd

    Thanks Simon - lookup table has been updated.

    Compatible with all instances of Data Studio from v2.7.5 onwards

  • Henry Simms
    Henry Simms Administrator
    edited October 2022

    Updated the data domain with around 190 new country aliases, mainly Dutch country names:

    Standardise Country.dmxd

    The function now contains 1,773 aliases for 248 countries and territories.

    Compatible with Data Studio 2.8.9 and above.

  • Henry Simms
    Henry Simms Administrator

    Some further minor country name updates and a fix to one data row which was misaligned.

    Standardise Country.dmxd

    The function now contains 1,793 aliases for 252 countries and territories.

    Compatible with Data Studio 2.12.6 and above.

  • Henry Simms
    Henry Simms Administrator
    edited December 2023

    Cleaned up the country names / aliases domain to remove some invalid values.

    StandardizeCountry_20231812.dmxd

    Compatible with Data Studio 2.12.9 and above.

Categories