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?!
- Write Recipe Results to Multiple Datasets
- Create a Dataset Using a CSV File
- 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:
- If the Opportunity Amount is null, then categorize them as Not Started
- If Amount >= 10000, then categories such records as Hot
- If Amount < 10000 and Amount >= 5000, then categories such records as Warm
- 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.
The CASE statement is CRM Analytics’ way of handling if/then logic. The CASE statement is followed by at least one pair of WHEN and THEN statements. Use CASE in a for each statement to assign different field values in different situations.
case primary_expr(optional)
when condition
then result_expr
...
else
default_expr
end
- On the Data Manager app page, navigate to Data Manager | Recipes.
- Click on the recipe name OpportunitiesWithAccount to update it.
- To add a custom formula field to the recipe, we have to use the Transform node.
- 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 as mentioned below:
case When Amount is null then 'Not Started' When Amount >= 10000 then 'Hot' When Amount < 10000 and Amount >= 5000 then 'Warm' else 'cold' end
- In Output Type, select Text as the calculated column’s data type.
- In the formula editor, enter the formula as mentioned below:
- To add the formula as a step in the Transform node, click Apply.
- 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.
The syntax for the coalesce function in CRM Analytics is:
coalesce(expr1, expr2)
- Let’s update the dashboard’s table component to replace null with 0 for won column.
- Almost there! Once everything looks good, click the Update button.
- Repeat the above steps to replace the null value with 0 for the remaining columns. In the end, the dashboard will look like the following screenshot:
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.
One thought on “CRM Analytics: Replace Nulls With Specified Values”