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
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
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';
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
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';
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
33. Use matches() for contain - TEXT
case when "abcd" matches "abc" then "found" else "not found" end
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 - TEXT
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 !('Product_Name' matches "cable") then "a" else "b" end
use ! as not
case when 'Product_Name' matches "cable" then "found" else "not found" end
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.use ! as not
Reference:
Hi Johan
ReplyDeleteI am using compute expression to get length of string len(Industry), but i am getting error.
Could you help me ?
what is the error message?
DeleteSince this is a numeric field, you need to set the Precision and Scale
DeleteHi Johan,
ReplyDeleteI have set Precision and scale it is working.
Could you help me how to find number of words using compute expression(which we can do easily with formulas), i have tried using 'replace' but not succeed. Any suggestions?
do you mean formula as in Salesforce formula field? share your formula here
DeleteI am new to SAQL, and am trying to add days to a date field. Ie, I want to calculate a a field date + (say) 6 months.
ReplyDeletehave you try to use _sec_epoch?
DeleteHi Johan - Thank you for putting this together.
ReplyDeleteI'm trying to change a column of values from negative to positive.
I tried using current('Field_Name')*-1. It didn't work.
What is the best way to accomplish this?
are you using computeExpression? if yes, no need to use current()
DeleteHi John I am trying to get weekdays using day_in_week('DateField') but getting error, How can I achieve this, kindly help... your suggestion will be valuable
ReplyDeletehave you try it with toDate() _sec_epoch?
Deletemore info https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_saql.meta/bi_dev_guide_saql/bi_saql_functions_dayinweek.htm
Hi John, I am trying to create a compute expression where I calculate the age of each stage based in case based on cas history object. Can you help me the expression, please.
ReplyDeleteI am not sure what is the result want to achieve, just the last aging or each status change, you also need to consider if user 'jump' status. But, all needs compute relative to calculate
DeleteHi John I'm using a Formula field of type currency in compute expression and I had selected type as numeric. while running the dataflow it failed. Please help
ReplyDeletewhat is the formula and what error?
DeleteHi..Can you pls help with the concatenate compute expression if I need to concatenate a string and numeric field together. Eg, the status of Compliance Request is "Approved" and the Time taken is 5 days.. I want a column to read - Approved (5).
ReplyDeleteI tried to do - 'Compliance_Process_Approval_Status__c' + " (" + 'ComplianceCycle' + ")"
But it didn't work.. I got this error - Incompatible types: STRING and NUMERIC in 'Compliance_Process_Approval_Status__c' + " (" + 'ComplianceCycle'
Let me know if you have any suggestions. Thanks
Hi, Can you help me with Compute expression to concatenate a string and a numeric value together.
ReplyDeleteI have a status field and a cycle time field that I need to concatenate to look like - Approved (5).
I tried - 'Compliance_Process_Approval_Status__c' + " (" + 'ComplianceCycle' +")"
but it didn't work.
The error I got is - Incompatible types: STRING and NUMERIC in 'Compliance_Process_Approval_Status__c' + " (" + 'ComplianceCycle'
Is there any way to achieve the result I need. Thanks
assume your end target is a string, have you try to use number_to_string()
Deletecheck this https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_saql.meta/bi_dev_guide_saql/bi_saql_functions_string_nts.htm