With Data Prep, you can add new columns by applying functions to existing columns in your dataset.
Work with the Compute tool¶
To access the Data Prep Compute tool, click compute in the project Tools bar:
Following is an overview of the elements in the Compute values pane:
|Compute tool||Click compute to access the Compute values pane.|
|for column field||Enter the name of the new column.|
|functions menu||Click to access the functions menu which contains a list of the supported functions. See How to add a computed column and Supported functions for details.|
|formula row||Use this row to build the formula to compute values for your new column. Enter simple formulas or build advanced formulas using columns and functions. This example shows the
Note: Data Prep is case sensitive. When typing column titles in the formula row, the case of your input must match the case of the column's name.
|computed column||The new column that will contain the result of your formula.|
|timer||The timer provides a countdown to indicate how long before the Data Preview refreshes. The timer appears after you pause your typing, and resets again after either you continue entering the expression or the grid refreshes. This feature is especially useful when you're constructing a large computed column expression and you don't want the grid to refresh after every key stroke. If you prefer to disable the timer, deselect the Auto-update check box adjacent to the new column field. An Update button is then available in place of the timer and clicking it manually refreshes the grid.|
How to add a computed column¶
To add a computed column to your project:
Click compute in the Tools bar.
The Compute values pane displays.
Enter the name of the new column in the for column field.
Use one of the following methods to add the function to the formula:
Click the functions icon at the top-left of the Compute values pane and select a function from the list:
In the formula row, press ESC and select a function from the list.
- In the formula row, type the function.
See Supported functions for information on the available functions.
Use one of the following methods to add the column between the parentheses in the formula:
- From the Data Preview, click the column name.
- In the formula row, type
@, then press ESC, and select the column from the list.
- In the formula row, type the name of the column between two
@characters. For example, if the column's title is Hire Date, type @Hire_Date@.
The new column with the computed values is committed to your project.
If there is an error in your formula, an error icon () displays in the Steps tool. In this case, you can create and save a lens but you cannot publish it to an AnswerSet.
- Put spaces around operator symbols, for example,
1 + 1not
Put double quotes around text, for example
If you need to literally type a quote sign, type a backslash before it. To type a backslash, type an additional backslash before it (because the backslash character needs to be escaped with a backslash). For example, to enter the text:
Go to "C:\windows"Type the following:
"Go to \"C:\\windows\""
The following is a list of supported functions. Select a function to see its description, syntax, and an example.
- Multiply, divide a column by any number
- Add to, subtract from a column by any number
- equal to
- greater than
- greater than or equal to
- less than
- less than or equal to
- not equal to
See Comparison operators for details.
If your organization has developed and installed custom functions, you can add new columns by applying a custom function to existing columns in your dataset. See Custom computed column functions for details.