Einstein Analytics: Handling null values

This blog is not related to Null Handling in Measures. As per this article, null values in Dimensions are not completely supported in Einstein Analytics, however, we often deal with null values in many scenarios.

To prevent data quality issues, Einstein Analytics will disregard any fields in Salesforce (or columns in external data) that are entirely null.

Grouping with Null
Date field by default is null, when you use it in a chart for grouping, null value will not be shown. To overcome this, if you pull data from Salesforce, set a defaultValue (e.g. 1900-01-01) to override in sfdcDigest.

Dimension field by default is null, when you use it in a chart for grouping, null value will not be shown. To overcome this, if you pull data from Salesforce, use defaultValue (e.g. NA, " is not needed) to override in sfdcDigest.

Measure field by default is 0 for null value. We cannot use the Measure field for grouping, but we can use it for the filter.


Dataflow
If you need to filter out null value for Dimension, you can't use the following in Filter node:
  Field1__c != "" && Field2__c != "" <-- this will not filter any
  Field1__c is not null && Field2__c is not null <-- this will throw error

Solution: use defaultValue in sfdcDigest (e.g. NULL, " is not needed) to override null. So, your filter nodes would be Field1__c != "NULL" && Field2__c != "NULL".
The 2nd option, adding compute expression nodes to check case when Field1__c is null then "No" else "yes" end, then use Filter nodes to check the ComputeExpression field for "Yes".

Filter null records in SAQL
q = load "DTC_Opportunity_SAMPLE";
q = filter q by 'Product_Name' is null;
q = foreach q generate 'Account_Owner' as 'Account_Owner', 'Product_Name' as 'Product_Name';
q = order q by 'Account_Owner';
q = limit q 10;

Filter null records in SAQL
q = load "DTC_Opportunity_SAMPLE";
q = foreach q generate 'Account_Owner' as 'Account_Owner', 'Product_Name' as 'Product_Name', (case when 'Product_Name' is null then "" else 'Product_Name' end) as 'PM';
q = filter q by PM == "";
q = order q by 'Account_Owner';
q = limit q 10;

Count Not null records in SAQL
q = load "Lead";
q = foreach q generate (case when 'SFDC_Lead_ID__c' is null then "" else 'SFDC_Lead_ID__c' end) as 'SFDC_Lead_ID__c';
q = filter q by 'SFDC_Lead_ID__c' != "";
q = group q by all;
q = foreach q generate count() as 'count';

To group null as NA
q = load "DTC_Opportunity_SAMPLE";
q = foreach q generate coalesce('Product_Name',"NA") as 'Product_Name', count() as count ;
q = group q by 'Product_Name';
q = foreach q generate 'Product_Name' as 'Product_Name', count() as count;
q = order q by count desc;


Augment transformation cannot find the parent will cause Null

SAQL Expression in above computeExpression case when 'Acq.Industry__c' is null then "Parent not available" else 'Acq.Industry__c' end

Data in Salesforce

Result in Einstein Analytics

Notes from above screenshot:

• 1st row for Parent Industry = NA, because we set the default value to NA in sfdcDigest.
• 1st and 2nd row for Account Source = null, because we set the default value to null in sfdcDigest.
• 1st row for Employees and 2nd row for Annual Revenue = 0, Einstein Analytics auto set 0 for Measure field with a null value.
• 3rd and 4th row for Parent Industry = Parent not available, this is because there is parent lookup value, we use computeExpression to set the value, this is different with 1st row, where the parent lookup value is available, but Industry for that parent record is null.

Last update: 23 Sep 2019

Reference:

• Enable Null Measure Handling
• Limitations of Null Values in Dimensions in Einstein Analytics
• Null Handling in Einstein Analytics
• Overriding Salesforce Field Metadata

Salesforce: Account Hierarchy columns & Recently Viewed columns

Can we customize the Account Hierarchy columns?

Classic - NO
This is the article and this is the idea.

Lightning - YES
Here is the article and here the steps:

• From Setup, at the top of the page, select Object Manager.
• In the Account object, click Hierarchy Columns and then click the New button (if this never created), or Edit link to edit existing columns.
• You can add up to 15 columns.

Once create Hierarchy Columns, the system will auto-create a list view called Org_Account_Hierarchy in Account. The list view will have the same column as fields added to Hierarchy Columns.

You can rename this list view, but not able to change the sharing setting (this list view is visible to all users). Deleting this list view will reset the Account Hierarchy columns back to the default. Updating columns on this list view will change columns in Account Hierarchy too.

By default -- no hierarchy column setup, account hierarchies display the same columns as the Recently Viewed Accounts standard list view. However, this Recently Viewed Accounts list view columns don’t change when you customize the hierarchy columns.

Recently Viewed
In Classic, when we click a tab, such as the Accounts tab, by default it will show "Recent Accounts" with columns defined in Search Layouts - Tab

While switching to Lightning, clicking the Accounts tab will bring open the "Recently Viewed" list view (if pinned list view has not changed), list view columns in "Recently Viewed" is defined in Search Layouts > Default Layout, you can edit to change columns to display, but you can't delete, change the visibility, or rename it.

You also can change columns to display for each profile, once configured, this will overwrite columns to display from the default layout.

However, there is another list view with a similar name but include object name in Lightning, "Recently Viewed object name"e.g. Recently Viewed Accounts, we cannot configure the columns for this view (as of Summer '20 release), and are unable to delete, change the visibility, or rename it. Check out this article for more information.

Reference:

• Unable to customize the standard 'Account Hierarchy' view in Classic UI
• Set Up Account Hierarchy in Lightning Experience
• Select Fields to Display in the Recently Viewed List on Object Home Pages in Lightning Experience
• Duplicate view when viewing 'Recently Viewed' objects in Lightning

Einstein Analytics: SAQL samples in computeExpression

computeExpression is one of the most powerful features in Dataflow in computeExpression, you can "add" fields without having to change the source data.

Keywords such as: and, like, case, etc. are case-sensitive.

1. Get field value - TEXT
'CreatedBy.Role.Name'
 as the field name contains a dot, always use ' before and after the field name

2. Set a text value - TEXT
"RoleName"
 always use " before and after the value for text

3. Get current date - DATE
now() 
returns the current datetime in UTC

4. Get the first 18 characters - TEXT
substr('RECORD_ID', 1, 18)
 use substr() function

5. Get the first 18 characters with len() - TEXT
substr(UltimateParentPath, len(UltimateParentPath)-17,18)
use len() function

6. Concatenate text - TEXT
'CreatedDate_Year' + "-" + 'CreatedDate_Month' + "-" + 'CreatedDate_Day'
  use + to concatenate string

7. Concatenate text in case - TEXT
case when isDuplicate is null then 'Name' else 'Name' + " (" +'Username'+ ")" end
 use case function

8. Using multiple when in Case and compare Text - TEXT
case when 'Opportunity.Sales_Type__c' == "A" then "Type A" 
     when 'Opportunity.Sales_Type__c' == "B" then "Type B"  
     else "Type C" 
end
 use == to compare equal and use != to compare not equal

9. Check is Null - TEXT
case when 'Opportunity.Name' is null then "Yes" else "No" end
use is null keyword

10. Check is Not Null - TEXT
case when 'OptySplit.SplitOwnerId' is not null then 'OptySplit.SplitOwnerId' else 'OwnerId' end
use is not null keyword

11. Use && and ! as alternative - TEXT
case when 'Owner.Name' is null && !('Queue.Name' is null) then "Queue" 
     when !('Owner.Name' is null) then "User" 
     else "N/A" 
end
 use || as OR; use && as AND

** You can't use || or OR for the same field, so use IN instead

12. Use && and ! as alternative to get field value - TEXT
case when 'Owner.Name' is null && !('Queue.Name' is null) then 'Queue.Name' 
     when !('Owner.Name' is null) then 'Owner.Name' 
     else "N/A" 
end

use ! as NOT

13. Simple bucketing - TEXT
case 
  when Value == 0 then "[1] 0"
  when Value <= 1000000 then "[2] 0-1M"
  when Value <= 25000000 then "[3] 1M-25M"
  when Value <= 100000000 then "[4] 25M-100M"
  else "[5] 100+M"
end

use <= , < , > , >= for Numeric or Date

14. Get numeric value from field - NUMERIC
case 
  when Type_Data is not null and Type__c == "Type A" then Annual_Data
  when Type_Value is not null and Type__c == "Type B" then Annual_Value
end
else keyword is not a must in case, ' is not a must if the field does not contain dot

 use and or, but not AND OR

15. Check Neglected Case - TEXT
case when DaysSinceLastActivity >= 60 then "true" else "false" end

16. Check Is Lost - TEXT
case when 'IsClosed' == "true" && 'IsWon' == "false" then "Yes" else "No" end
there is NO BOOLEAN type in Einstein Analytics, so always enclosed wth "

17. Using IN - TEXT
case when 'Opportunity.StageName' in ["Stage 1", "Stage 2", "Stage 3", "Stage 4"] then "true" else "false" end

case 
  when AnnualRevenue < 1000000 then "Low"
  when AnnualRevenue in [1000000..2000000] then "Medium"
  when AnnualRevenue > 2000000 then "High"
end

q = foreach q generate 'Account.Name' as 'Account.Name', 'Account.Owner.Name' as 'Account.Owner.Name', (case when !('Account.Owner.Name' in ["Adam Lee","David Lee"]) then "Someone else" else "Adam or David" end) as 'Owner Check';

 use in[ ] and ! function

18. Check is Overdue - TEXT
case when ('IsClosed' == "false") && (daysBetween(toDate(substr('ActivityDate', 1, 10), "yyyy-MM-dd"), now()) > 0) then "true" else "false" end
use daysBetween() to return the number of days between two dates 

 use toDate() to convert a string or epoch seconds to date -- this sample convert from a string

19. Get Days Overdue - NUMERIC
case when 'IsOverdue' == "true" then daysBetween(toDate(substr('ActivityDate', 1, 10), "yyyy-MM-dd"), now()) else 0 end

20. Check is between 2-30 days - TEXT
(case when date('TIMESTAMP_DERIVED_Year', 'TIMESTAMP_DERIVED_Month', 'TIMESTAMP_DERIVED_Day') in ["30 days ago".."2 days ago"] then "yes" else "no" end)
use date() to return date with input of a year, a month, and a day dimension

21. Check is Yesterday - TEXT
(case when date('TIMESTAMP_DERIVED_Year', 'TIMESTAMP_DERIVED_Month', 'TIMESTAMP_DERIVED_Day') in ["1 day ago".."current day"] then "yes" else "no" end)

22. Check is Past Due - TEXT
case when IsClosed == "false" && (toDate(CloseDate_sec_epoch) < now()) then "true" else "false" end

 use toDate() to convert a string or epoch seconds to a date -- this sample convert from a _sec_epoch

 
23. Duration in Second - NUMERIC
date_diff("second", toDate(ValidFromDate_sec_epoch), now())
use date_diff() to return the amount of time between two dates, the time interval could be: year, month, quarter, date, week, hour, minute, second

24. Check Is Closed - TEXT
case when daysBetween(toDate(ActivityDate_sec_epoch), now()) >= 0 then "true" else "false" end

You also can use date_diff() for the above formula

case when date_diff("day", toDate(ActivityDate_sec_epoch), now()) >= 0 then "true" else "false" end

25. Get days since last activity - NUMERIC
case    
   when LastActivityDate is null then daysBetween(toDate(LastModifiedDate_sec_epoch), now())   
   when LastModifiedDate > LastActivityDate then daysBetween(toDate(LastModifiedDate_sec_epoch), now())   
   else daysBetween(toDate(LastActivityDate_sec_epoch), now()) 
end

26. Get Past Due Date - NUMERIC
case when IsClosed == "false" && (toDate(CloseDate_sec_epoch) < now()) then daysBetween(toDate(CloseDate_sec_epoch), now()) else 0 end

27. Get Opportunity Age - NUMERIC
case when IsClosed == "false" then daysBetween(toDate(CreatedDate_sec_epoch), now()) else daysBetween(toDate(CreatedDate_sec_epoch),toDate(CloseDate_sec_epoch)) end

28. Get Lead Age - NUMERIC
case when ('IsConverted' == "false") then daysBetween(toDate(CreatedDate_sec_epoch), now()) else daysBetween(toDate(ConvertedDate_day_epoch), toDate(CreatedDate_day_epoch)) end

29. Get Case Duration - NUMERIC
case when ('IsClosed' == "true") then ('ClosedDate_sec_epoch' - 'CreatedDate_sec_epoch')/86400 else ('CurrentDate_sec_epoch' - 'CreatedDate_sec_epoch')/86400 end

30. Get Opportunity Age - NUMERIC
case 
when IsClosed == "true" && CloseDate_day_epoch - CreatedDate_day_epoch > 0 then CloseDate_day_epoch - CreatedDate_day_epoch
when IsClosed == "true" && CloseDate_day_epoch - CreatedDate_day_epoch <= 0 then 0
else round((date_to_epoch(now()) - CreatedDate_sec_epoch) /86400,0)
end

in the above sample, we check:
1). if the opportunity is Closed and Closed Date is after Created Date, then Age = Closed Date - Created Date
2). if the opportunity is Closed and Closed Date is before or equal Created Date, then age = 0
3). if the opportunity is Open, then age = now() - Created Date

Another option to calculate the age for (1) is to use date_diff() function:
date_diff("day", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch))

Please note that limitation of the maximum amount of time returned from date_diff() is 9,223,372,036,854,775,807 nanoseconds or 106,751.99 days (> 292 years).

 use date_to_epoch() converts a date to epoch seconds.

 use round(n [,m]) return the value of n rounded to m decimal places, m can be negative.

31. Converting Created Date to PST - DATE
toDate('CreatedDate_sec_epoch'-3600*8)

32. Using starts_with(), ends_with, and lower() to compare string - TEXT
case
  when starts_with(lower(Subject),"call") then "Call"
  when ends_with(lower(Subject),"call") then "Call"
  else "Others"
end

 the one contain full string must be at left, it is case sensitive, so use lower() to help

33. Use matches() for contain - True/False
case when "abcd" matches "abc" then "found" else "not found" end

 the full sentence must be at left, this operator is not case-sensitive, requires at least two characters

case when 'Product_Name' matches "cable" then "found" else "not found" end 

 this will work

case when "cable" matches 'Product_Name' then "found" else "not found" end 

 this is not allowed with error Invalid function argument: 'Product_Name', the second operand must be text.

case when !('Product_Name' matches "cable") then "a" else "b" end 
 use ! as not

34. Use like() for contain - True/False

Customer_Name like "%ni%"

This query matches names that contain "ni" such as Anita Boyle, Annie Booth, Derek Jernigan, and Hazel Jennings.

Customer_Name like "ne%"

This query match returns names that end with "ne".

!(Customer_Name like "%po%")

Use with ! to exclude records. above query shows all customer names that don’t contain "po".

35. Use index_of() to return position a character - NUMERIC

index_of("Hawaii", "a") --> return the first occurrence of "a", starting at the beginning --> result is 2

index_of("Hawaii", "a",1,2) --> return the second occurrence of "a", starting at the beginning --> result is 4

index_of("Hawaii", "a",3) --> return the first occurrence of "a", starting at the third position --> result is 4

The function returns 0 if the search string is not found.

36. Use substr() to return a substring that starts at the specified position - TEXT

substr("CRM", 1, 2) --> 2 characters long, starting at position 1 --> "CR"

substr("CRM", -2, 2) --> 2 characters long, starting from the *end* of the string --> "RM"

substr("Salesforce", 3) --> the first 3 characters --> "Sal"

Use case: find the country of the following San Francisco, CA, United States

substr(fieldname, -1 * (len(fieldname) - index_of(fieldname, ",", 1, 2) - 1))

37. Relative Date filter

CreatedDate >= "LAST_N_DAYS:90"

38. Numeric to Text

In a scenario where you need to use a numeric field for a chart or list widget, you can't do it; unless to convert the field to text.

case when 'CaseHistory.OwnerChangeCount' is null then "00" else number_to_string('CaseHistory.OwnerChangeCount', "00") end

39. Filter by Date

In a scenario, we would like to filter only records with created dates after Sep 1, 2024; we will use dateRange() and put the end date as far as possible.

date(CreatedDate_Year, CreatedDate_Month, CreatedDate_Day) in [dateRange([2024,9,2], [2029,12,31])]

40. Day of the Week

To get which day of the week, the following formula will return 5 for Thursday

day_in_week(toDate(CreatedDate_sec_epoch))

41. MOD

MOD or modulo is the remainder when dividing. For example, “5 mod 3 = 2” which means 2 is the remainder when you divide 5 by 3. In Excel would be MOD(5,3) = 2, but in SAQL

5 % 3 ---> 2

Reference:

• Arithmetic Operators
• Comparison Operators
• Logical Operators
• String Operators
• case
• date_diff()
• Date Functions
• Day in the Week, Month, Quarter, or Year

Einstein Analytics: using Allow disjoint schema to transform dataset

Here is the use case, we have multiple columns for each type to store value, this cause we can't really easily build a chart when the values are spread across many columns.

Solution: to transform the data source by splitting into many rows and using 1 column.

Dataflow

Inside computeExpression computeTYPE1 nodes:

Inside Type_TYPE1 Computed Field:

this is text, which is the field name

Inside Value_TYPE1 Computed Field:

this is numeric, which is the field value

Do the same for computeExpression Type 2 and Type 3. Then, combine all the data using append node

Once we have all the values spread across rows, use sliceDataset transformation to drop the original Type 1, Type 2, Type 3 fields.

Reference: 

• Data out of Shape? Dataflow Append Doesn't Mind
• append Transformation

Salesforce: User current app

Question: is there a way to check what is the user current app?

Answer: yes, but only for Lightning.


UserAppInfo
Since API version 38.0, Salesforce introduces an object called UserAppInfo, this object stores the last Lightning app users logged in to.

Sample query: SELECT Id, UserId, AppDefinitionId, FormFactor, CreatedById, CreatedDate, LastModifiedById, LastModifiedDate FROM UserAppInfo WHERE UserId = '00580000004JEfS'

Notes:
- AppDefinitionId: the ID of the last Lightning app that the user logged in to.
- FormFactor: The relative size of the app as displayed, values are:
     Small—suitable for a small device like a mobile phone
     Medium—suitable for a tablet
     Large—suitable for a large display device, like a monitor

Since AppDefinitionId is updateable, this means we can mass update the users' app.


AppDefinition
This object represents the metadata of an app and its navigation items. This object is available in API version 43.0 and later.

Sample query: SELECT DurableId, Label, UiType, Description, DeveloperName, MasterLabel, NavType, UtilityBar FROM AppDefinition ORDER BY Label

Notes:
- DurableId: instead of Id, DurableId represent App Id as in use for UserAppInfo
- UiType: option: Aloha for Classic, and Lightning
- NavType: option: Standard and Console
- UtilityBar: only available for Lightning


Reference:

• UserAppInfo
• AppDefinition