This article is about how to use the synonym table in Ref Mart in order to convert country names to ISO3 codes. The Country to ISO3 Lookup table serves an ongoing need to assign the correct/official ISO3 code to a country when the raw data contain text in the name field. It is very common the country name to be written in different languages and make use of different names like Netherlands – Holland, Germany – Deutschland or common mix ups like United Kingdom and Great Britain.
The solution so far was to overcome this manually in a pipeline like shown in the examples below, which required effort and time, and of above all it was something that needed to happen in every mart facing the same situation.
The country Synonym table offers a centralized solution that can be utilized by any mart owner who needs to overcome the issue described above. It is consisted of two columns “INPUT_COUNTRY_NAME” and “OUTPUT_ISO3” as shown below.
With the use of the Country Synonym table, a centralized solution is offered, which can be utilized by running a simple MartLookup command containing the MartCode attribute as shown below.
Breakdown of the code:
<MartLookup MartCode="REFMART" LookupTable="COUNTRY_TO_ISO3_LOOKUP" SourceColumns="COUNTRY" LookupColumns="INPUT_COUNTRY_NAME" LookupResultColumn="OUTPUT_ISO3" SourceResultColumn="COUNTRY_FK">
</MartLookup>
1. <MartLookup
2. MartCode=”REFMART”: The MartCode command points to the RefMart
3. LookupTable=”COUNTRY_TO_ISO3_LOOKUP”: The synonym table is set us as the LookupTable
4. SourceColumns=”COUNTRY”: Is the column name of the uploaded data, containing the country names. This varies according to the uploaded file.
5. LookupColumns=”INPUT_COUNTRY_NAME”: The input of the synonym table
6. LookupResultColumn=”OUTPUT_ISO3”: The output of the synonym table
7. SourceResultColumn=”COUNTRY_FK”>: The column with the result. The name may change according to how the mart model is created.
8. </MartLookup >
An actual example of how to use the code above can be found in the Pipeline Examples Mart by clicking here
Note: Except of the commands in lines 4 and 7, which needs to be adapted the rest of the code can be used as is.