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

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"

Reference:

• Arithmetic Operators
• Comparison Operators
• Logical Operators
• String Operators
• case
• date_diff()
• Date Functions