Linking Tables

One of the main bottlenecks to actually linking and using this data is the failure of organizations (even those within the UN community) to adopt and implement a common standard for spelling country and sub-national area names and the failure to use the standard ISO codes with these names. Without these standards, it is difficult linking data at the national level, but with 200 or so countries, it is manageable but extremely time consuming. The addition of sub-national data has made linking data extremely difficult. Geocoded (GIS) shapefile data is not an issue because the data is already geocoded. Data base systems require a common field in the "rows" being connected from different tables. In the case of these data bases the common field would be a country name or an agreed upon country code. While all the databases have a field for country name, the spelling of these names must be exactly the same-- data bases will not recognize Cote d'Ivoire as being the equivalent of Côte d'Ivoire--the smallest difference means that the fields will not link automatically and have to be linked by hand. Country names are an unfortunate choice for a common field because many of the names are complicated and even when an attempt to enter the name correctly is made, spelling errors are common. In addition, the data base providers often use the name by which a country is commonly known, rather than its official name -- for example it is much more convenient to enter "Bolivia" than the the more formal "Bolivia, Plurinational State of".

The wide variety of practice in using country codes means that there is no one solution to linking data from different tables. The solutions proposed will be covered under two headings --1) where both tables include an ISO code in the table and 2) When one or both of the tables in question do not include an ISO code. To link the tables, software which includes database functions will be required to use the lookup table effectively (Access, LibreOffice Base, some of the later versions of Excel, etc.), although linking of small tables can be done manually.

For instructions and suitable lookup tables for linking data tables which use different versions of the ISO codes go to mainlu1 and for solutions to linking tables where one or both do not have an ISO code go to mainlu2. Use this option only for linking by country names.

Copyright ©1998/2019 GRI    Updated 21 May 2019