Compute columns¶

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:

	Element	Description
	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 MAX function. Notice the column titles are enclosed between two 'at' (@) signs. If there is an error in your formula, an error message appears below the formula row explaining the error. 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:

1. Click compute in the Tools bar.

   The Compute values pane displays.

2. Enter the name of the new column in the for column field.

3. 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.

4. 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@.

5. Click Save.

   The new column with the computed values is committed to your project.

Syntax rules¶

• Put spaces around operator symbols, for example, 1 + 1 not 1+1

• Put double quotes around text, for example "Hello".

  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\""

Supported functions¶

The following is a list of supported functions. Select a function to see its description, syntax, and an example.

Date/Time functions¶

• DATE
• DATEADD
• DATEDIFF
• DATEFORMAT
• DATETRUNC
• DATEVALUE
• DAY
• DAYOFWEEK
• DAYOFYEAR
• ENDOFMONTH
• FROMUNIXTIME
• HOUR
• MAXDATE
• MIDNIGHT
• MINDATE
• MINUTE
• MONTH
• NETWORKDAYS
• NOW
• QUARTER
• SECOND
• SETTIMEZONE
• TODAY
• WEEKOFYEAR
• WORKDAY
• YEAR

Informational functions¶

• FIRSTNONBLANK
• ISBLANK
• ISDATE
• ISNULL
• ISNUMBER
• ISTEXT

Logical functions¶

• AND
• IF
• IFERROR
• NOT
• OR

Mathematical functions¶

• Multiply, divide a column by any number
• Add to, subtract from a column by any number
• ABS
• CEILING
• EXP
• FACTORIAL
• FLOOR
• INT
• LN
• LOG
• LOG10
• MOD
• POWER
• ROUND
• ROUNDDOWN
• ROUNDPERC
• ROUNDUP
• SIGN
• SQRT
• SUM

Statistical functions¶

• AVERAGE
• MAX
• MEDIAN
• MIN
• MODE
• STDEV
• STDEVP
• VAR
• VARP

Text functions¶

• CHAR
• CONCATENATE
• FIND
• HASHVALUE
• LEFT
• LEN
• LOWER
• MID
• PADLEFT
• PADRIGHT
• REGEXP
• REPEAT
• REPLACE
• REVERSE
• RIGHT
• SEARCH
• STR
• SUBSTITUTE
• TRIM
• TRIMLEFT
• TRIMRIGHT
• UPPER
• VALUE

Comparison operators¶

• equal to
• greater than
• greater than or equal to
• less than
• less than or equal to
• not equal to

See Comparison operators for details.

Custom functions¶

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.