Hierarchical data is data such as places which has a Country, Region, Town etc.

This article describes how to use the HierarchyLookup command to lookup data which is hierarchical and to resolve synonyms.

The command can also clean values before matching them (NormalizeText), and match values that are similar but not identical (SimilarityMatching).

Typical use case

You have case data with free-text subnational administrative place names. Each case record has these fields: Admin0Raw (ex: Country), Admin1Raw (ex: Province), Admin2Raw (ex: District).

You need to validate and match the freetext names with a reference table of geoplaces. Let’s call the reference table for geography REF_GEOPLACES. We will first show how to do a hierarchy lookup directly against the REF_GEOPLACES table, and then we will show how to use a synonym table to translate any freetext spelling variations to the reference table.

Example Table CASES (your input data)

Table REF_GEOPLACES

Typical geography reference table

The goal is to create a new PlaceID column in the input CASES table to hold the value from the matching PlaceID column from the REF_GEOPLACES table. Any failed lookup will be reported as a batch issue.

Note that matching is case insensitive.

Here is how to write such a lookup command. “Source” refers to the input CASES table. “Lookup” refers to the reference REF_GEOPLACES table.

  <Transform>
    <HierarchyLookup LookupTable="REF_GEOPLACES" RegisterMissingAsIssues="true">
      <Lookups LookupResult="ResolvedId" SourceResult="PlaceID">
        <Lookup Source="Admin0Raw" Lookup="Admin0" LookupFilter="GeoLevel = 2" />
        <Lookup Source="Admin1Raw" Lookup="Admin1" LookupFilter="GeoLevel = 3"  />
        <Lookup Source="Admin2Raw" Lookup="Admin2" LookupFilter="GeoLevel = 4"  />
      </Lookups>
    </HierarchyLookup>
  </Transform>

The order of the <Lookup> elements is defining the actual hierarchy of parent - child relationships. They must be ordered from the higher level to the lower one.

After resolution

In this example, we only requested the final PlaceID of the resolved geography (see last column at the end).

The command is also able to add a new column for each level of lookup that has been resolved. In each Lookup command, indicate which column from the reference table (LookupResult) should be put into which column of the cases table (SourceResult), as follows:

  <Transform>
    <HierarchyLookup LookupTable="REF_GEOPLACES" RegisterMissingAsIssues="true">
      <Lookups>
        <Lookup Source="Admin0Raw" Lookup="Admin0" LookupFilter="GeoLevel = 2" LookupResult="Admin0Name" SourceResult="ResolvedAdmin0" />
        <Lookup Source="Admin1Raw" Lookup="Admin1" LookupFilter="GeoLevel = 3" LookupResult="Admin1Name" SourceResult="ResolvedAdmin1" />
        <Lookup Source="Admin2Raw" Lookup="Admin2" LookupFilter="GeoLevel = 4" LookupResult="Admin2Name" SourceResult="ResolvedAdmin2" />
      </Lookups>
    </HierarchyLookup>
  </Transform>

All LookupResult and SourceResult in <Lookups> or <Lookup> are optional.

Support for Synonyms

The HierarchyLookup command can be configured to use a synonym table. A table of synonyms maps spelling variations in the source to the to fixed values in the lookup table.

Table REF_SYNONYMS example data

In this example, both “Benin” and “Bénin” map to ISO3 code BEN. The value “BEN” must be in the lookup reference table. The synonym table is used in the HierarchyLookup command like this:

  <Transform>
    <HierarchyLookup LookupTable="REF_GEOPLACES" RegisterMissingAsIssues="true">
      <SynonymTable Code="REF_GEOSYNONYMS" FromColumn="Synonym" ToColumn="Reference" Filter="Category = 'Geoplaces'" />
      <Lookups LookupResult="ResolvedId" SourceResult="PlaceID">
        <Lookup Source="Country"  Lookup="Admin0" LookupFilter="GeoLevel = 2" />
        <Lookup Source="Province" Lookup="Admin1" LookupFilter="GeoLevel = 3" />
        <Lookup Source="District" Lookup="Admin2" LookupFilter="GeoLevel = 4" />
      </Lookups>
    </HierarchyLookup>
  </Transform>

Note that the column names of the synonym table can be anything as they are configurable. Also, the “Category” column in the synonym table is optional but is useful if different kinds of synonyms for different purposes are stored in the same synonym table.

Table REF_SYNONYMS suggested data model

For example, if there are mixed ISO3 and country names in the same columns. Synonyms for Country name to ISO3 can be added.

Synonyms disambiguation thanks to children

What happens if the same synonym maps to different reference values?

For example, consider the following raw input data. AUS is ambiguous, as it could refer either to Australia (city = Sydney) or to Austria (city = Innsbruck).

The HierarchyLookup command is able to correctly resolve this using this synonym table:

• Table REF_Synonyms

How does HierarchyLookup know that sometimes AUS should map to Australia and sometimes map to Austria? It knows because synonyms are only resolved in the context of its parent and child values. In other words, HierarchyLookup doesn’t look at each level in isolation of the other; it looks at the combined result across all levels.

Example reference table Table REF_PLACES

In the reference table, only Australia has values NSW and Sydney and only Austria has values Tyrol and Inssbruck. During the lookup, the combination of these values will determine uniqueness of the result.

If a regular, non-hierarchical lookup (1 level only) is set up to only resolve admin level 0 (country), it won’t be able to resolve the country AUS as it would match multiple entries due to synonyms for AUS having 2 possible outcomes.

But by doing a lookup on admin level 0 and also admin level 1 and also admin level 2, HierarchyLookup will use the deeper level to resolve the upper one.

<Lookups LookupResult="ResolvedId" SourceResult="PlaceID">
  <Lookup Source="Country"  Lookup="REF_Country" LookupFilter="REF_Level = 1" />
  <Lookup Source="Province" Lookup="REF_Province" LookupFilter="REF_Level = 2" />
  <Lookup Source="District" Lookup="REF_City" LookupFilter="REF_Level = 3" />
</Lookups>

Here is how the matching process will look like.

1. For row with id=333, trying to match first Lookup Country: input data = 'AUS'
2. Augment with synonyms found for In = 'AUS' -> ('Austria', 'Australia')
3. Found 2 entries in REF_PLACES for condition REF_Country in ('AUS', 'Austria', 'Australia') and REF_Level = 1, so no match yet.
4. Go to next level Lookup Province, input data = 'NSW'
5. Augment with synonyms found for In = 'NSW' -> ()
6. Found 1 single entry in REF_PLACES for condition REF_Province in ('NSW') and REF_Level = 2, so match. ✅
7. If match, go back to parent lookup and resolve to NSW’s Ref_Country -> ‘Australia’

Resolution of missing parents

Similarly to above disamiguation of missing parents. If input data contains some row like

But the reference REF_PLACES contains a unique entry for REF_Country in ('AUS', 'Austria', 'Australia') and REF_Province = 'NSW' and REF_Level = 2. The HierarchyLookup will be able to resolve the country of this row to ‘Australia’.

For this resolution of empty values to be enabled, the attribute <Lookups TryIfEmptyParent="true" /> needs to be set to true. By default, it’s set to false so won’t try to resolve such rows.

Output table

Using NormalizeText

The examples in this section and the next one use a different data set: country names and polio case numbers (EPIDs).

Let’s say you receive case numbers (EPIDs). The same case number is not always written the same way: an extra dash, a leading zero, a year on 4 digits instead of 2.

NormalizeText cleans the text before it is matched, so these small differences don’t block the lookup.

Example Table STORING_DATA (your input data)

Table EPIDs

The reference table holds one row per case: the country and the case number (EPID). For demo purposes, the NORMALIZED_VALUE column shows the normalized value of each case number. It does not have to be in the table: the lookup computes it, so we don’t use it.

The goal is to link each EPID of the input table to its case in the EPIDs table, even when it is not written exactly the same way.

Here is how to write such a lookup command. The <NormalizeText> element goes inside the <Lookup> element and only applies to that level. In this example it is used together with <SimilarityMatching>, which is explained in the next section.

  <Transform>
    <HierarchyLookup LookupTable="EPIDS" RegisterMissingAsIssues="ErrorRemoveRows">
      <Lookups TryIfEmptyParent="true" LookupResult="ID" SourceResult="MATCHED_ID">
        <Lookup Source="COUNTRY_RAW" Lookup="COUNTRY" />
        <Lookup Source="EPID_RAW" SourceResult="FOUND_EPID" LookupResult="EPID" Lookup="EPID">
          <NormalizeText Accents="Remove" LeadingZeros="RemoveAll" Punctuation="Remove" Years="ReduceTo2Digits" />
          <SimilarityMatching AutoMatchThreshold="80" ManualMatchThreshold="70" RejectDigits="true" />
        </Lookup>
      </Lookups>
    </HierarchyLookup>
  </Transform>

Note that HierarchyLookup also works with a single <Lookup> level. Here, the COUNTRY level limits the search to the cases of the same country, which helps because an EPID does not always contain the country.

Without NormalizeText

If the lookup has no <NormalizeText> and no <SimilarityMatching>, like this one:

  <Transform>
    <HierarchyLookup LookupTable="EPIDS" RegisterMissingAsIssues="ErrorRemoveRows">
      <Lookups TryIfEmptyParent="true" LookupResult="ID" SourceResult="MATCHED_ID">
        <Lookup Source="COUNTRY_RAW" Lookup="COUNTRY" />
        <Lookup Source="EPID_RAW" SourceResult="FOUND_EPID" LookupResult="EPID" Lookup="EPID">
        </Lookup>
      </Lookups>
    </HierarchyLookup>
  </Transform>

only the values written exactly as in the EPID column are found:

The other five are reported as failed lookups, and someone would have to add a synonym for each of them.

NormalizeText options

Each option controls one kind of cleaning. An empty <NormalizeText /> uses the default values.

Note that matching is case sensitive after NormalizeText: A and a are different. Use ChangeCase="Upper" if the case can vary in your data.

After resolution

HierarchyLookup tries three steps, in this order, and stops at the first one that finds a match:

1. Exact match: the value, as written in your file, is compared with the lookup column.
2. Synonyms: the value is looked up in the synonym table.
3. Similarity: the value is compared with the lookup column after NormalizeText, and each row gets a score.

With the lookup command shown above, using <NormalizeText> and <SimilarityMatching>, all seven case numbers are found:

AFG/07/20/HC-026 and ANG-BEN-CUB-25-005-C-1 are written exactly as in the EPID column, so they are found at step 1. The other five are not, so they go on to step 3. Each one scores high enough against a case of its country to be matched automatically.

Using SimilarityMatching

SimilarityMatching allows matching of values that are similar but not identical, for example a spelling mistake in a country name. This avoids creating synonyms for small spelling variations.

Each possible match gets a similarity score from 0% to 100%. Depending on the score, the value is matched automatically, the data loader chooses from a list of suggestions, or it is reported as a normal lookup issue.

Example Table STORING_DATA (your input data)

Table GEO_DATA

Here is how to write such a lookup command. Like NormalizeText, <SimilarityMatching> goes inside the <Lookup> element, so you can use it on some levels and not on others.

  <Transform>
    <HierarchyLookup LookupTable="GEO_DATA" RegisterMissingAsIssues="ErrorRemoveRows">
      <SynonymTable Code="SYNONYM" FromColumn="SOURCE" ToColumn="TARGET" />
      <Lookups TryIfEmptyParent="true">
        <Lookup Source="COUNTRY_RAW" SourceResult="COUNTRY_ID" LookupResult="ID" LookupFilter="LEVEL_ID = 0" Lookup="NAME">
          <NormalizeText />
          <SimilarityMatching AutoMatchThreshold="80" ManualMatchThreshold="70" />
        </Lookup>
      </Lookups>
    </HierarchyLookup>
  </Transform>

How the thresholds work

With AutoMatchThreshold="80" and ManualMatchThreshold="70":

After resolution

With the lookup command shown above, Portugals is not in the GEO_DATA table, but it is similar enough to Portugal to be matched automatically. COUNTRY_ID gets the value 0 (Portugal), and no lookup issue is reported:

What the data loader sees

Lookup issues are resolved in the Issues tab of the batch (see Resolve Lookups in UI).

Without SimilarityMatching, the list of references shows all the values of the lookup table. For example, with this lookup command:

  <Transform>
    <HierarchyLookup LookupTable="GEO_DATA" RegisterMissingAsIssues="ErrorRemoveRows">
      <SynonymTable Code="SYNONYM" FromColumn="SOURCE" ToColumn="TARGET" />
      <Lookups TryIfEmptyParent="true">
        <Lookup Source="COUNTRY_RAW" SourceResult="COUNTRY_ID" LookupResult="ID" LookupFilter="LEVEL_ID = 0" Lookup="NAME">
        </Lookup>
      </Lookups>
    </HierarchyLookup>
  </Transform>

In this example, PRT is not found in GEO_DATA. The list of references shows all five countries, in alphabetical order.

With SimilarityMatching, the list only shows the values that score at or above ManualMatchThreshold, with their score. See the case numbers in RejectDigits below for an example.

If no value reaches ManualMatchThreshold, the list is empty. With the lookup command below, no country is similar enough to PRT:

  <Transform>
    <HierarchyLookup LookupTable="GEO_DATA" RegisterMissingAsIssues="ErrorRemoveRows">
      <SynonymTable Code="SYNONYM" FromColumn="SOURCE" ToColumn="TARGET" />
      <Lookups TryIfEmptyParent="true">
        <Lookup Source="COUNTRY_RAW" SourceResult="COUNTRY_ID" LookupResult="ID" LookupFilter="LEVEL_ID = 0" Lookup="NAME">
          <NormalizeText />
          <SimilarityMatching AutoMatchThreshold="80" ManualMatchThreshold="26" />
        </Lookup>
      </Lookups>
    </HierarchyLookup>
  </Transform>

Note that the score compares the spelling, not the meaning. PRT is the code of Portugal, but it is not similar to “Portugal”. Codes still need synonyms. Synonyms are checked before SimilarityMatching, so a synonym always wins.

Adding a synonym when there is no suggestion

With the lookup command shown at the start of this section (ManualMatchThreshold="70"), and an empty SYNONYM table, PRT is reported as a failed lookup. The list of references is empty, because no country scores 70% or more:

Typing Portugal in the box does not help. With SimilarityMatching, the list only contains the suggestions, so Portugal cannot be selected from it:

The first list, in the Resolution column, decides what the + button does:

• PRT as synonym: adds PRT to the synonym table.
• PRT to GEO_DATA: adds PRT as a new row in the lookup table.

To add the synonym, keep PRT as synonym selected in the first list and click the + button. The Add new SYNONYM window opens, with PRT already filled in as the Source. Type Portugal as the Target and click Save:

The synonym is saved in the SYNONYM table. Load the file again: this time PRT is found through the synonym, and there is no failed lookup for GEO_DATA anymore. Only the case numbers, which are not in the EPIDS table, are still reported:

RejectDigits

By default, RejectDigits is true: if at least one digit is different, the score is 0. This avoids suggesting District 2 for District 1, or a different case number.

Let’s say you receive these case numbers, which are not in the EPIDS table:

With RejectDigits="true":

  <Transform>
    <HierarchyLookup LookupTable="EPIDS" RegisterMissingAsIssues="ErrorRemoveRows">
      <SynonymTable Code="SYNONYM" FromColumn="SOURCE" ToColumn="TARGET" />
      <Lookups TryIfEmptyParent="true" LookupResult="ID" SourceResult="MATCHED_ID">
        <Lookup Source="EPID_RAW" SourceResult="FOUND_EPID" LookupResult="EPID" Lookup="EPID">
          <NormalizeText Accents="Remove" LeadingZeros="RemoveAll" Punctuation="Remove" Years="ReduceTo2Digits" />
          <SimilarityMatching AutoMatchThreshold="80" ManualMatchThreshold="70" RejectDigits="true" />
        </Lookup>
      </Lookups>
    </HierarchyLookup>
  </Transform>

PRT-BEN-CUB-25-005-C2 gets one suggestion: ANG-BEN-CUB-25-005-C2 (79%), which has the same digits. The other case numbers of the EPIDS table have different digits, so they are not suggested. In the same way, PRT-BEN-CUB-25-005-C1 gets only ANG-BEN-CUB-25-005-C-1 (79%).

With RejectDigits="false":

  <Transform>
    <HierarchyLookup LookupTable="EPIDS" RegisterMissingAsIssues="ErrorRemoveRows">
      <SynonymTable Code="SYNONYM" FromColumn="SOURCE" ToColumn="TARGET" />
      <Lookups TryIfEmptyParent="true" LookupResult="ID" SourceResult="MATCHED_ID">
        <Lookup Source="EPID_RAW" SourceResult="FOUND_EPID" LookupResult="EPID" Lookup="EPID">
          <NormalizeText Accents="Remove" LeadingZeros="RemoveAll" Punctuation="Remove" Years="ReduceTo2Digits" />
          <SimilarityMatching AutoMatchThreshold="80" ManualMatchThreshold="70" RejectDigits="false" />
        </Lookup>
      </Lookups>
    </HierarchyLookup>
  </Transform>

The same row now gets two suggestions. Besides ANG-BEN-CUB-25-005-C2 (79%), it also gets ANG-BEN-CUB-25-005-C-1 (71%), which is a different case number (C1 instead of C2). In the same way, PRT-BEN-CUB-25-005-C1 now also gets ANG-BEN-CUB-25-005-C2 (71%).

Keep RejectDigits="true" for codes and case numbers, so that the data loader is not offered the wrong case.

Lowering ManualMatchThreshold

In the two examples above, PRT-LIS-CUB-25-005-C3 gets no suggestion at all: “No results found”. Its best score is below 70, the value of ManualMatchThreshold.

If ManualMatchThreshold is lowered to 20:

  <Transform>
    <HierarchyLookup LookupTable="EPIDS" RegisterMissingAsIssues="ErrorRemoveRows">
      <SynonymTable Code="SYNONYM" FromColumn="SOURCE" ToColumn="TARGET" />
      <Lookups TryIfEmptyParent="true" LookupResult="ID" SourceResult="MATCHED_ID">
        <Lookup Source="EPID_RAW" SourceResult="FOUND_EPID" LookupResult="EPID" Lookup="EPID">
          <NormalizeText Accents="Remove" LeadingZeros="RemoveAll" Punctuation="Remove" Years="ReduceTo2Digits" />
          <SimilarityMatching AutoMatchThreshold="80" ManualMatchThreshold="20" RejectDigits="false" />
        </Lookup>
      </Lookups>
    </HierarchyLookup>
  </Transform>

the same row now gets two suggestions:

ManualMatchThreshold decides how long the list of suggestions is:

• A high value shows only the values that are very close. The list is short, and it can be empty.
• A low value shows more values, including ones that are not really similar. Here, both suggestions are different case numbers.

Choose a value that shows the data loader the likely matches, without filling the list with wrong ones.