CRM Analytics: Replace Nulls With Specified Values

Last Updated on December 11, 2022 by Rakesh Gupta

Big Idea or Enduring Question:

• How do you replace null with specified values when working with Datasets or Recipes?

Objectives:

After reading this blog, you’ll be able to:

• Work with CASE Statements or coalesce formula
  
• Apply logic to replace the null value with the specified value
• and much more

In the past written a few articles on CRM Analytics. Why not check them out while you are at it?!

1. Write Recipe Results to Multiple Datasets
2. Create a Dataset Using a CSV File
3. Data Security to Control Access to Rows

A null value is a field that is blank and signifies missing or unknown values. NULL values often appear in data sets. They could be purposely blanks or simply incomplete data. NULL values can affect the results of our computations differently depending on how you use them.

Let’s start with business use cases and understand how to handle null values in recipes and datasets using different functions. In the end, you will clearly understand how to deal with null value while working on CRM Analytics.

Business Use case I

Donna Serdula is a System administrator at Gurukul On Cloud (GoC). After reading this article, she created a recipe and a few datasets. Now she got a new requirement to categorize the data based on the following conditions: 

1. If the Opportunity Amount is null, then categorize them as Not Started
2. If Amount >= 10000, then categories such records as Hot
3. If Amount < 10000 and Amount >= 5000, then categories such records as Warm
4. Otherwise, categorize them as cold

Automation Champion Approach (I-do):

This is a very common scenario and may have various solutions for it. We will use CASE Statements to solve it. 

case primary_expr(optional)
	when condition
	then result_expr
	...
	else
	default_expr
end

1. On the Data Manager app page, navigate to Data Manager | Recipes.
2. Click on the recipe name OpportunitiesWithAccount to update it.
3. To add a custom formula field to the recipe, we have to use the Transform node.
   1. Hover over a node to select the Transform node.
4. To create the formula for the calculated column, select the Formula button in the Transform toolbar and select Custom Formula.
   1. In the formula editor, enter the formula as mentioned below:
      Copy Code Copied Use a different Browser

      
      case
          When Amount is null
          then 'Not Started'
          When Amount >= 10000
          then 'Hot'
          When Amount < 10000 and Amount >= 5000
          then 'Warm'
          else
          'cold'
      end
      

   2. In Output Type, select Text as the calculated column’s data type.
5. To add the formula as a step in the Transform node, click Apply.
6. Almost there! Once everything looks good, click the Save and Run button.

Business Use case II

Donna Serdula is a System administrator at Gurukul On Cloud (GoC). After spending a few days on Trailhead and other websites, she was able to create a dashboard that displays Opportunity group by Industry and status (custom formula), as shown below: 

Automation Champion Approach (I-do):

This is many possible solutions for the above scenario.  We will use coalesce function to solve it. Useful for providing a default value if a function returns a null value.

coalesce(expr1, expr2)

Formative Assessment:

I want to hear from you!

What is one thing you learned from this post? How do you envision applying this new knowledge in the real world? Feel free to share in the comments below.