computeExpression Transformation

The computeExpression transformation enables you to add derived fields to a dataset. The values for derived fields aren’t extracted from the input data source. Instead, CRM Analytics generates the values using a SAQL expression, which can be based on one or more fields from the input data or other derived fields. For example, you can use an expression to assign a value to a field, concatenate text fields, or perform mathematical calculations on numeric fields.

Note To create a calculated column based on a formula in a Data Prep recipe, see Formula Transformation: Create a Calculated Column Based on an Expression. To do it in a Data Prep Classic recipe, see Add a Formula Field in a Recipe.

The computeExpression transformation and computeRelative transformation are similar, but they have a key difference. The computeExpression transformation performs calculations based on other fields within the same row. The computeRelative transformation performs calculations based on the previous and next values of the same field in other rows.

Consider these guidelines when creating a computeExpression transformation.

• You can include only the following SAQL operators and functions in the expression:
  • Arithmetic operators
  • Case operator
  • String operator
  • Date functions
• Multivalue fields aren’t supported. The dataflow fails if you include a multivalue field in the SAQL Expression parameter.
• The values of the derived field must match its specified type. For example, set the type of the derived field to Text if the values are strings.
• CRM Analytics calculates the values of derived fields in the order in which they are listed in the JSON. Thus, if you create a derived field based on other derived fields in the same computeExpression transformation, the input-derived fields must be listed first. For example, Derived_A must be listed before Derived_B in the following computeExpression transformation JSON snippet:

  "CreateDerivedFields": {
     "action": "computeExpression",
     "parameters": {
        "source": "sourceNode",
        "mergeWithSource": false,
        "computedFields": [
           {
              "name": "Derived_A",
              "type": "Text",
              "label": "Derived Field A",
              "saqlExpression": "\"hello \""}, 
           {
              "name": "Derived_B",
              "type": "Text",
              "label": "Derived Field B Dependent on Field A",
              "saqlExpression": "Derived_A + \"world\""}
           ]
        }
     }

• You can choose whether the resulting dataset includes only the derived fields, or includes the input and derived fields.
• If the expression produces an overflow value for a numeric field, the transformation sets the field value to the default value specified for the column. If no default value is set and null handling is disabled, the field value is set to 0. If no default value is set and null handling is enabled, it’s set to null. (See the documentation about null measure handling.) Also, if the expression defines multiple columns and the expression produces an overflow value, all generated fields are set to the default value. To isolate the issue to the field with the overflow value, create a separate computeExpression transformation for each defined column.

Example

Let’s look at an example. You want to create a dataset based on Salesforce opportunity data. You create a dataflow that extracts the Id and Amount fields from the Opportunity object. In addition, you also want to add the following derived fields to the dataset: ModifiedId, SalesTax, FinalPrice, and ValueCategory. For the derived fields, you:

• Append “SFDC” to each opportunity Id to get a new modified Id.
• Calculate the sales tax based on an 8% tax rate.
• Calculate the final price by adding the amount and sales tax.
• Categorize opportunities into low-, medium-, and high-value buckets based on the calculated final price.

You create the following dataflow definition.

{
   "salesData": {
      "action": "sfdcDigest", 
      "parameters": {
         "object": "Opportunity", 
         "fields": [
            {"name": "Amount"},
            {"name": "Id"}]}},
   "Derived_Fields": {
      "action": "computeExpression", 
      "parameters": {
         "source": "salesData", 
         "mergeWithSource": true, 
         "computedFields": [
            {
               "name": "ModifiedId", 
               "type": "Text", 
               "saqlExpression": "\"SFDC\" + Id"},
            {
               "name": "SalesTax", 
               "type": "Numeric", 
               "precision": 18, 
               "defaultValue": "0", 
               "scale": 5, 
               "saqlExpression": "Amount * 0.08"},
            {
               "name": "FinalPrice", 
               "type": "Numeric", 
               "precision": 18, 
               "defaultValue": "0", 
               "scale": 5, 
               "saqlExpression": "Amount + SalesTax"},
            {
               "name": "ValueCategory", 
               "type": "Text", 
               "saqlExpression": "case when FinalPrice < 1000 then \"Low\" when FinalPrice >= 1000 and FinalPrice < 2000 then \"Medium\" else \"High\" end"}
         ]
      }
   },
   "Register_CategorizedSales": {
       "action": "sfdcRegister",
       "parameters": {
           "alias": "Categorized_Sales",
           "name": "Categorized_Sales",
           "source": "Derived_Fields" }
   }
}

• computeExpression Parameters
  When you define a computeExpression transformation, you set the action attribute to computeExpression. You also specify the parameters for the input source and the expression used to generate the values.