Returns the value of a field or a fixed value based on the value of a field. For every unique value in the selected field, another source field for the output can be selected. In addition a static text value can be set instead of the value from another source field.
In this example the values from the source field Type is used to determine what the value for the output is. In case the Type is ‘Hardware‘, the value from the Product field is used. In case the type is ‘Software‘ the value from source field Version is used. In any other cases the value from Type is used.
Configuration
Field – Select the field from the source which values needs mapping. Immediately after selecting a field, the first 10.000 results (rows) are used to get all unique values from the source. They are added to the Fields Mapping grid in the Input column.
Output field name – The name of the newly added field.
Use value ranges – Leave off to map one source value to one target value. Turn on to map a value range to one target value.
Fields Mapping – This is where the actual mapping is defined:
- Add – Manually add a row to the Fields Mapping grid.
- Paste from clipboard – Add a list of values from a spreadsheet or a CSV-formatted text file. Copy the values from any file and paste them by using the button. When one column is copied, the Input field is filled with the values. When two columns are copied, both the Input and Output fields are filled. Duplicates found in pasted data are automatically removed and a warning will show the number of removed rows.
Note: Pasting from the clipboard will delete all existing items. - To delete an item, select the checkbox at the end of a mapped item. The Delete button will be shown. Click it to delete the selected item(s).
Copy unknown value to output field – If the field contains a value that is not assigned, should this value be copied to the output field? If not, output field will be left blank.
Notes – Optional description to describe the reason this transformation is added. Convenient for later reference.
A maximum of 10.000 rows can be added to the Fields Mapping grid.
Use value ranges
When one to one mapping is not convenient, there is an option to map a range of values to one output value. This uses a from and to value to create a range.
Switch on the Use value ranges option and select the Data type that the source values represent.
- Number – the from and to values can only contain numbers, both negative and positive
- Text – the from and to values can contain all characters; values are case-insensitive
- Date – the from and to values can only contain date time values
In the above example the goal is to get the name of a sales representative based on the difference in turnover for the current year compared to last year. If the turnover is more than 100% less than last year, the Region Manager is the person to contact the customer. If the turnover is between 100% and 25% less than last year, then the Account Manager is responsible for contacting the customer. And if the turnover is between 25% less or 5% more than last year, the inside sales team will take care of contacting the customer. However, if the customer outperforms by selling between 50% and 100% more than last year, the Account Manager will contact then, while the Region Manager will contact them with compliments if they outperform by more than 100%.
The Output column shows the corresponding fields from the source, the preview shows the values (names in this example) from the corresponding fields.