You are here:
Formula Transformation: Create a Calculated Column Based on an Expression
In Salesforce Data Pipelines, create a column in a Data Prep recipe that displays values based on a formula calculation. The calculation can include input from other fields in the same row or across rows. For example, you can create a Profit column based on input from Revenue and Cost columns. Enter formulas in EA-SQL format. EA-SQL is a collection of standard and custom functions for numeric, string, and date data.
Required Editions
Formula Transformation: Create a Calculated Column Based on an Expression
| User Permissions Needed | |
|---|---|
| To create a recipe: | Edit CRM Analytics Dataflows OR Edit Dataset Recipes |
- In a Transform node of a Data Prep recipe, select a column in the Preview tab.
- To create the formula for the calculated column, select the Formula button (
) in the Transform toolbar and select Custom Formula. 
- In the formula editor, enter the formula and add parameters using the correct
syntax.
As you type in the formula editor, functions and columns matching the name appear for you to select to add to the formula.
Note The best practice is to use double quotes around all column names. For a value that is a string literal, use single quotes. .Double quotes are required when referencing dataset columns that share a name with reserved keywords.
To get help, check out all functions and columns in the Functions and Columns tabs. To add one to the formula, select it and click +.
Functions are listed in the Functions tab by category: Window Functions, String Functions, Numeric Functions, Date/Time Functions, and Additional Functions. To view the window functions, enable Multiple row formula.
- For formulas with window functions, specify the column used to partition the rows in
Partition By, column used to sort the data in Order
By, and sort direction in Sort Direction.
For example, set Partition By to Opportunity Name, Order By to Last Modified Date, and Sort Direction to Ascending. Based on these settings, the transformation combines all records into partitions (think buckets) based on the same opportunity name. It then sorts the records in each partition in ascending order based on the last modified date.
- Identify each column in the formula by its API name. Type in the API name or select the
column from the Columns tab.
- In Output Type, select whether the calculated column’s data is text, numeric, date, or datetime.
- Based on the output type, you can set these attributes.
Attribute Description Precision The maximum number of digits in a numeric (measure) column. Includes all numbers to the left and to the right of the decimal point (but excludes the decimal point character). Enter an integer from 1 through 18, inclusive. If a numeric value exceeds the specified precision, the value is set to 0 or null, depending on whether null measure handling is enabled. For example, if precision is set to 5 and null measure handling is enabled, the numeric value 123456.78 is replaced with null.
Scale The number of digits to the right of the decimal point in a numeric column. Enter an integer from 0 through 17, inclusive. The scale must be less than the precision. For example, if the numeric value is 123456.789 and you set the scale to 2, the number appears as 123456.78.
Length The maximum number of characters in a text (dimension) column. Enter an integer from 1 through 32000, inclusive. The default length is 255 characters.
Date Format The date format of a date column.
- In Default Value, choose the value to show if your expression returns no results.
- If needed, you can change the label of the calculated column.
- To add the formula as a step in the Transform node, click
Apply.
The calculated column appears in the recipe preview.
- To view the Graph area, click the Collapse button (
). - Save the recipe.
You can bucket a formula column or reference it in later formulas defined in the recipe. Common functions have their own buttons in the Transform toolbar that automatically fill in the formula with the column and predefined syntax. The toolbar shows appropriate functions based on the type and number of selected columns. To use one, click the function button in the Transform toolbar (1). The formula is started for you (2), using the column and function you selected.
- Date Functions for Formulas
In Salesforce Data Pipelines, use date functions to adjust or calculate values from dates in your recipe. For example, you can add time, find time until, and extract a day of the week from date fields. The arguments for date functions are date columns. - Numeric Operators and Functions for Formulas
In Salesforce Data Pipelines, use numeric functions to calculate values from numbers in your recipe. For example, you can round, truncate, and determine the absolute value of a number. The arguments for numeric functions can be number values or columns. - String Functions for Formulas
Use string functions to create values based on other strings. For example, you can change the casing of text strings, concatenate values from multiple columns, and replace values. The arguments for string functions must be text strings or dimension fields. - Window Functions for Formulas
In Salesforce Data Pipelines, use window functions in a Data Prep recipe to perform calculations across rows. For each row of input data, perform a calculation on a selection—or window—of related rows. Unlike aggregation that groups rows, window functions return a result for each row. For example, you can calculate the changes to the opportunity amount throughout the stages of each opportunity to see if you can hit your sales target. - Multivalue Functions for Formulas
In Salesforce Data Pipelines, use multivalue functions with your multivalue data. For example, look for a specific value, count how many values there are, create a new multivalue by combining other text, convert a multivalue to a string, or split a string and return a multivalue. The arguments for multivalue functions must be text strings or fields. - Additional Functions for Formulas
In Salesforce Data Pipelines, these versatile functions can be used with the data types text, number, date_only, and datetime to build logic into your formulas.
