Skip to main content
Question

Data Lake, converting Country code

  • September 11, 2023
  • 3 replies
  • 79 views
ajminton
Contributor I

Here’s a Data Lake questions.  In the DEPT_36245898717.analytics_rms.vw_person_addresses table , there’s a Country field, which is a number.  I would like to convert that number to the name of the matching Country.  For example, the United States is always 840 and Canada is 124, etc.  What table  within the Data Lake is available to convert the number to the Country?  I checked a few places like the attributes , but couldn’t find something to link to.  TIA

    This topic has been closed for replies.

    3 replies

    J
    Contributor II
    • Jaycee.Elliott
    • Contributor II
    • September 12, 2023

    Hi @ajminton,

     

    I have not been able to find a dimensional attribute table providing the country name derived from the country code either. My best guess is that these codes are derived from international standards set for statistical purposes by the United Nations Statistics Division (UNSD). In the standard list, US = 840 and Canada =124. The full list can be found here: 

    Country Codes (worldbank.org)

    It can be easily ingested into an SQL environment to perform the desired lookup.

    @Mark43 please confirm. Some metadata/governance would be highly appreciated for these types of inquiries.


    Best,

    Jaycee

    ajminton
    DavidSchwindt
    Mark43 Team Member
    Forum|alt.badge.img
    • DavidSchwindt
    • Mark43 Team Member
    • September 12, 2023

    I have checked with our locations engineering team and confirmed they use the country codes as described in the ISO 3166 international standard and as linked by @Jaycee.Elliott in the previous message. However, the list is not available in the Mark43 database. I think it would make sense for us to add a REF table with these codes and I will take investigating that as an action item.

    Thank you for engaging here and I’ll follow up in this thread!

    ajminton
    ajminton
    Contributor I
    • ajminton
    • Author
    • Contributor I
    • September 12, 2023

    Thanks David.  Adding it to the REF table will help.  AJ

    Terms & ConditionsCookie settingsAccessibility statement