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 .
|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
You can use a filter to run the operation only on data that meets certain criteria.
For details, see Using Filters with Operation.
To preview the results that you can obtain with the operation, click Run preview. The program will show you how the first 100 rows in your table will look like after the operation is executed. Please note that it may take some time to generate a preview.
- Click Save. The Edit Operation window will close and the new operation will appear in the Operations pane.
Using types Field and Constant
Task: Combine fields with first and last names into a new column named Employee.
Solution: Under New column value, specify the substrings to be copied: value Name of type Field, value space of type Constant, and value Surname of type Field (in that order). The space constant will separate the name from the surname, so that you don't get JohnSmith, for example.
Using type Case
Task 1: Add a new column that will contain the status of an insurance policy — valid or invalid. The Insurance column contains some code. If a code starts with 100 or 215, the insurance policy is invalid. Any codes starting with 349 are valid. Other codes require additional verification.
Solution: Specify the above conditions and for any insurance policies that do not start with 349, 215 or 100, write Verification required in the new column.
Result: The table below shows the original data and the results obtained by running the operation.
Task 2: Add a new column named Client that will contain a de-identified string instead of a client's name.
Solution: Create a new operation and specify Client for the name of the column. Under New column value, select the value Name of type Case (this is the column that contains clients' names). Then click Add condition, specify a Contains . condition (see note below), and specify Personal information as the string to be written into the new column. Now if the Name field contains at least one character, its value will be changed to Personal information and this new value will be written into the new column.
Note: In the Contains . condition, the "." stands for any character. For a detailed treatment of the available operators, see Extract substring > Operators.