Example: Harmonizing County Data for UK Charging Sites

We have downloaded UK government data on electric vehicle charging sites, which we want to join to our sales data, aggregated by UK county.

Unfortunately, the downloaded data has some missing and inconsistent values for county names, so that many values do not match our master data for counties, and the County column cannot be reliably used to aggregate the data by county or join the table to location data. For example, the county Argyll and Bute is entered in several different ways in our electric vehicle charging data:

Electric Vehicle Charging Entity County Column Value

Geo Master Data County Name Column

Argyll

-

Argyll & Bute

-

Argyll and Bute

Argyll and Bute

Once we've loaded the raw data into SAP Datasphere:

Step

Result

We create a view to:
  • Remove unneccessary columns

  • Clean up some column names

  • Create a new column to calculate the total wattage available in each station.

We create an intelligent lookup, add our view as the input entity, and set the pairing column to County, as this is the column that contains the data that should be joined with our lookup entity.

We add our Geo view as the lookup entity, and select the County ID and County Name columns as return columns.

We create a fuzzy rule to compare county names in each entity.

We deploy and run the intelligent lookup and our rule is able to match just under half the records.

We review and process the remaining records in the Preview Data panel.