The Map values operation changes entries within a column to a desired value. Common use cases include:
Standardising entries in your source data, such as company names, to entries in
your data warehouse: for example mapping all instances of ‘British Petroleum’ in a column to ‘BP’.
Adding a new column of risk codes to a dataset: for example, mapping all instances
of ‘Property’ in a column to ‘GI000002’.
The map values operation works on a single column. To map values in additional columns, use additional ‘Map values’ operations.
Map values workspace
Source values (Map from)
Select the column of values to map from. Quantemplate autosuggests from the values within the source column.
Mapped values (Map to)
Enter the value you wish to map to. Quantemplate provides smart mapping suggestions ranked by the the strength of the likely match.
Output settings
Define the output column and how to handle unmatched values.
Source column
Select a source column from the list of columns in source datasets.
Autofill source values
Automatically create new entries from the first 100 unique values in the column.
To add more source values individually, click on the blank row and select from the list of values in the dataset
Set match type
By default the match type is ‘equals’, select to change to ‘contains’.
Enter mapped values
Type the value you wish to map to, or select from Quantemplate's suggestions
Operation name
Click to edit the operation name and description.
Write mapped values to
Select whether to overwrite the original values with the mapped
values, or write the mapped values to a new column or overwrite another existing column.
If values are unmatched
Where values have not been matched, select whether to leave these values unchanged, or overwrite with a new value.
About suggested mappings
The Quantemplate suggestion algorithm ranks suggestions by considering:
mappings defined throughout your organisation
the similarity of the 'map-from' and 'map-to' values
mappings defined in an organisation seed file
Contact Quantemplate support for more information
and to configure an organisation seed list.
Using Map Values
Select the column containing the values you wish to map.
Define the ‘map-from’ value. Click in the source field and the popup
provides suggestions from the first 2000 distinct values in the column.
If desired, change the match type from ‘equals’ to ‘contains’
if you want a mapping to apply where any part of the source value matches the ‘map-from’ value specified.
Now enter the desired mapped value. Quantemplate provides
suggestions based on previous mappings.
To add another mapping, click on the blank entry below and repeat the
process.
Finally configure the output settings. By default the mapped values will be written to the same column. To
write the values to a new column, enter the new column name. Select whether to leave unmatched values unchanged or change to a new value.
Working with blank values
Sometimes the source dataset contains blank values – entries where no value has been entered. These can be mapped to another value (e.g. ‘Unknown’) by selecting the item (Blank) from the list of map-from suggestions. Alternatively if you wish to map a known value to a blank, select (Blank) from the list of map-to suggestions.
Autofill
Click the autofill button to automatically create new entries from the first 100 unique
values in the source column.
Example
Mapping company name
Your data sources all use different names for the company
NEWCO. The Map Values operation changes these values to match the value in your data warehouse.
Values in source data
Output values
NewCo Ltd
NEWCO
NewCompany Ltd
NEWCO
NewCo
NEWCO
Example
Mapping to risk codes in a new column
Source data contains column of Class of Business with three values:
Property, Engineering, Marine. Based on these values each row can be assigned a risk code in
a new column (GI Policy Ref) to enable the data to be processed in a Catastrophe Risk Model.
