In some scenarios, we need to return a Date field from computeExpression node.
Date value can be null
sample:
case when ClosedDate_sec_epoch > date_to_epoch(now()) then toDate(ClosedDate,"yyyy-MM-ddTHH:mm:ss.000Z") end
or
case when ClosedDate_sec_epoch > date_to_epoch(now()) then toDate(ClosedDate_sec_epoch) end
Date or Date/Time
For a Numeric field, Precision and Scale are required, while for a Date field, Date Format is required in computeExpression. Einstein Analytics by default will "explode" a Date field into:
- Year
- Quarter
- Month
- Week
- Day
- Hour
- Minute
- Second
- Epoch days
- Epoch seconds
Let us see some samples of computeExpression returning Date type. In this sample, ClosedDate is a Date/Time field in Salesforce:
(1) SAQL Expression: toDate(ClosedDate,"yyyy-MM-ddTHH:mm:ss.000Z")
Date Format: yyyy-MM-ddTHH:mm:ss.000Z
--> this will create DateTime field in Einstein Analytics
(2) SAQL Expression: toDate(ClosedDate_sec_epoch)
Date Format: yyyy-MM-ddTHH:mm:ss.000Z
--> this will create DateTime field in Einstein Analytics
(3) SAQL Expression: toDate(ClosedDate,"yyyy-MM-ddTHH:mm:ss.000Z")
--> this will create Date field in Einstein Analytics
(4) SAQL Expression: toDate(ClosedDate,"yyyy-MM-dd")
Date Format: yyyy-MM-ddTHH:mm:ss.000Z
--> this will cause an error because ClosedDate in this sample is a date time.
(5) SAQL Expression: toDate(ClosedDate_sec_epoch)
Date Format: yyyy-MM-dd
--> this will create Date field in Einstein Analytics, similar to (3)
(6) SAQL Expression: ClosedDate
Date Format: yyyy-MM-dd
--> this will cause an error, we can't use the field directly, even it is a date field
(7) SAQL Expression: now()
Date Format: yyyy-MM-dd
--> this is okay, it will return the UTC date
As the screenshot above, the date format determines how the values are stored in Einstein Analytics. But, let us drill further:
Even hour, minute, and second are not added in the date format then not shown in the date field, Einstein Analytics still maintains hour, minute, and second in the exploded fields.
yyyy-MM-ddTHH:mm:ss.000Z is similar with yyyy-MM-ddTHH:mm:ss.SSSZ and similar with yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
Now, let us try to use Date field from Salesforce, instead of Date/Time field. CloseDate here is a Date field in Salesforce:
(1) SAQL Expression: toDate(CloseDate,"yyyy-MM-dd")
Date Format: yyyy-MM-ddTHH:mm:ss.000Z
--> this will create DateTime field in Einstein Analytics
(2) SAQL Expression: toDate(CloseDate_sec_epoch)
Date Format: yyyy-MM-ddTHH:mm:ss.000Z
--> this will create DateTime field in Einstein Analytics
(3) SAQL Expression: toDate(CloseDate,"yyyy-MM-ddTHH:mm:ss.000Z")
Date Format: yyyy-MM-dd
--> this will cause an error, because CloseDate is a date field, not Date/Time field in Salesforce
(4) SAQL Expression: toDate(CloseDate_sec_epoch)
Date Format: yyyy-MM-dd
--> this will create DateTime field in Einstein Analytics
(5) SAQL Expression: toDate(CloseDate,"yyyy-MM-ddTHH:mm:ss.000Z")
Date Format: yyyy-MM-ddTHH:mm:ss.000Z
--> this will cause an error, similar to (3), CloseDate is a date field, not Date/Time field
(6) SAQL Expression: toDate(CloseDate,"yyyy-MM-dd")
Date Format: yyyy-MM-dd
--> this will create Date field in Einstein Analytics
In summary:
- for SAQL Expression, it is easier to use toDate(xxxx_sec_epoch)
- for Date Format, use yyyy-MM-ddTHH:mm:ss.000Z for Date/Time, or use yyyy-MM-dd for Date field
JSON:
If you download the dataflow JSON, you will see an additional \ before " in saqlExpression, but you can simply ignore that in the dataflow UI.
sample:
"saqlExpression": "toDate('CreatedDate', \"yyyy-MM-ddTHH:mm:ss.000Z\")",
"format": "yyyy-MM-dd",
Side Note: can we refer to a field in computeExpression within the same note?
Yes, as long as the field referred to is located above (in order) from the field call it, see screenshot below, CloseDate_2 can use CloseDate_1, but not CloseDate_4 and CloseDate_6.
