CRM Analytics: Recipe formula syntax in Transform node

For those who have used Dataflow in the past and migrated to Recipe recently, some of the syntax and functions are different from SAQL in Dataflow.

1. string
use ' instead of "

in SAQL, case when 'Data.Name' is null then "No" else "Yes" end

in the recipe, case when "Data.Name" is null then 'No' else 'Yes' end

2. operand in comparison

in the recipe, equal, use = instead of ==

unequal, use !=

3. operand logic

in the recipe, use AND or OR

4.  instr() to replace index_of() 
Syntax: instr(field, searchString, [position, [occurrence]])
instr('123!456!78!', !) -- the result is 4
instr('123!456!8!', !, [4])  -- the result is 7
instr('123!456!8!', !, [4, [2]])   -- the result is 9
case when instr(Email, '@ap') > 0 then 'APAC' when instr(Email, '@eu') > 0 then 'EMEA' else 'AMER' end

5. to_timestamp() to replace toDate()

in SAQL, case when 'Region' == "APAC" then toDate("2024-01-06 21:00:00") 

else toDate("2024-01-07 11:00:00") end

in the recipe, case when "Region" = 'APAC' then to_timestamp('2024-01-06 21:00:00') else to_timestamp('2024-01-07 11:00:00') end

The output type from to_timestamp is Date Time.

6. Formula in Compute Relative

Make sure the "Multiple row formula" is selected

in SAQL, case when previous(CaseId) is null  then "Yes" else "No" end

in the recipe, case when lag(CaseId) IS NULL then 'Yes' else 'No' end

7. format_number() to replace number_to_string()

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

in the recipe, case when "OwnerHistory.OwnerChangeCount" IS NULL then '00' else format_number("OwnerHistory.OwnerChangeCount", '00') end

8. Date/time comparison

in SAQL, case when ('CreatedDate_sec_epoch' >= StartDateTime_sec_epoch) && ('CreatedDate_sec_epoch' <= EndDateTime_sec_epoch) then "Yes" else "No" end

in the recipe, case when (to_unix_timestamp(CreatedDate) >= to_unix_timestamp(StartDateTime)) AND (to_unix_timestamp(CreatedDate) <= to_unix_timestamp(EndDateTime)) then 'Yes' else 'No' end

9. substr()

Formula substr() has no change in recipe

case when substr(OwnerId, 1, 3) = '00G' then 'Yes' else 'No' end

10. IN() function

in SAQL, Type in ["Transfer", "TransferredToSbrSkill"]

in the recipe, Type IN ('Transfer', 'TransferredToSbrSkill')

 

Reference:

• String Functions for Formulas
•  SAQL samples in computeExpression