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.

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

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"

Reference:

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