Pages

Wednesday, April 24, 2019

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:

26 comments:

  1. Hi Johan
    I am using compute expression to get length of string len(Industry), but i am getting error.

    Could you help me ?

    ReplyDelete
    Replies
    1. Since this is a numeric field, you need to set the Precision and Scale

      Delete
  2. Hi Johan,
    I 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?

    ReplyDelete
    Replies
    1. do you mean formula as in Salesforce formula field? share your formula here

      Delete
  3. I 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.

    ReplyDelete
  4. Hi Johan - Thank you for putting this together.
    I'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?

    ReplyDelete
    Replies
    1. are you using computeExpression? if yes, no need to use current()

      Delete
  5. Hi 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

    ReplyDelete
    Replies
    1. have you try it with toDate() _sec_epoch?
      more info https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_saql.meta/bi_dev_guide_saql/bi_saql_functions_dayinweek.htm

      Delete
  6. 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.

    ReplyDelete
    Replies
    1. I 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

      Delete
  7. Hi 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

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Hi, Can you help me with Compute expression to concatenate a string and a numeric value together.

    I 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

    ReplyDelete
    Replies
    1. assume your end target is a string, have you try to use number_to_string()
      check this https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_saql.meta/bi_dev_guide_saql/bi_saql_functions_string_nts.htm

      Delete
  10. Hello, this is a nice article.

    Wondering how I can add below statement into the filter transformation node?

    Say for example: CreateDate>2021-08-01

    Thank you

    ReplyDelete
  11. Hi, I have Order_Day, Order_Month, Order_Year in three different columns. I want to add these three columns and form OrderDate (type is Date)..can you help me on this..?

    ReplyDelete
  12. Hi, I'm trying to create a new column that gives me the greater value between 2 different date columns(PO DATE column vs FISRT CONFIRMED DATE column). I have created a DateDiff column between those 2 dates to do a conditional case formula like this:

    case
    when datediff(endDate, startDate) > 0
    then (First_Confirm_Date)
    when datediff(endDate, startDate) < 0
    then (PO_Date)
    else (First_Confirm_Date)
    end

    But I know it is not formulated correctly, what would be the right way to do it? I just need to know which one of those dates is greater and put it in a column.

    thanks!

    ReplyDelete
    Replies
    1. you can just simply use >= in the first "when" and use "else" for the rest

      Delete
  13. create a dataflow and then create a computed field on contact to identify the latest created contact for an account and mark it as primary contact for the respective account in dataflow tableau crm

    ReplyDelete
  14. -hi,

    I have 2 questions.
    1. I have a lens created which iam using MAP Chart. Iam counting the products sold to accounts based on Shipping Country. Some cases Germany is called as Deutschland. In that case i added a query that Case when shipping Country is Deutschland then Germany. It absolutely worked. But i ahve few accounts with shipping country as Germany. So my final table shows 2 Germany rows. i want to add the count of Rows for both. Is there any query i can try.
    2. I have field of text datatype where i store some Numbers and when i want sort it with that field it is not sorted as numbers generally do. Can i change the Datatype of the Field in Analytics Studio?

    ReplyDelete
    Replies
    1. 1. Assume you still use dataflow, you can create compute expression to combine it into 1 new field
      2. check this article to convert from dimension to measure https://dreamevent.secure.force.com/articleView?id=bi_integrate_data_prep_recipe_convert_dim_to_measure.htm&type=0

      Delete

Page-level ad