Question

Data Lake, converting Country code

  • 12 September 2023
  • 3 replies
  • 42 views
ajminton

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 comments

3 replies

J
Rank
Userlevel 3

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

DavidSchwindt
Rank
Userlevel 3
Badge

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

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

Terms & ConditionsCookie settings