English (English)
Derive field
The Derive field operation creates a new field that combines the values of other fields. The new string will be made up of substrings, which may be constants (i.e. strings of any characters), values from other columns, or new data added based on a rule. For example, you can use this operation to take a customer's first and last names from two different columns and place the combined full name into one column.
For more information about substrings, see Operations > About substrings.
Configuring the operation
- In the New column name field, specify the column into which combined data should be written. You can either provide a name for a new column of specify the name of an existing column.
Important! If you specify the name of an existing column, the operation will overwrite its data. - In the New column value field, specify the substrings to be copied into the column you specified in step 1. The operation will pick out the specified substrings and place them into that column.
To copy a substring, you also need to specify its Type and Value (see the table below for details). To add or remove a substring, click
or 
.
| Type | Description |
|---|---|
| Field | Copies data from a field. Value: The name of a field from which to copy data. |
| Constant | Adds a constant. Value: Any characters (space, digits, letters, etc.). |
| Case | Adds data based on a rule specified for a field. Value: Select a column name and specify a rule for adding a new substring. Case is an if-elseif-else statement. For each condition, specify Contains, Starts with, or Ends with and specify a new string after Then. This string will be placed into the new field if the original value meets the condition. Click Add condition to branch an if-elseif-else statement. See Using type Case below. |
- For your convenience, the Preview area will display the result of running the configured operation on the first row.
-
If required, you can use a filter and preview the results of the operation
- Click Save. The Edit Operation window will close and the new operation will appear in the Operations pane.
Configuration examples
Using types Field and Constant
Using type Case
9/22/2023 8:59:47 AM