Pages

Sunday, May 10, 2020

Einstein Analytics: computeExpression return Date

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

(2) SAQL Expression: toDate(ClosedDate_sec_epoch)
Date Format: yyyy-MM-ddTHH:mm:ss.000Z

(3) SAQL Expression: toDate(ClosedDate,"yyyy-MM-ddTHH:mm:ss.000Z")
Date Format: yyyy-MM-dd

(4) SAQL Expression: toDate(ClosedDate,"yyyy-MM-dd")
Date Format: yyyy-MM-ddTHH:mm:ss.000Z
this will cause error because date format with HH:mm:ss, while toDate() format string parameter does not have.

(5) SAQL Expression: toDate(ClosedDate_sec_epoch)
Date Format: yyyy-MM-dd

(6) SAQL Expression: ClosedDate
Date Format: yyyy-MM-dd
this will cause error, we can't copy directly even it is a date field 

(7) SAQL Expression: now()
Date Format: yyyy-MM-dd
this is okay, it will return UTC date and time


As the screenshot above, the date format determines how the values 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 maintain 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

(2) SAQL Expression: toDate(CloseDate_sec_epoch)
Date Format: yyyy-MM-ddTHH:mm:ss.000Z

(3) SAQL Expression: toDate(CloseDate,"yyyy-MM-ddTHH:mm:ss.000Z")
Date Format: yyyy-MM-dd
this will cause error

(4) SAQL Expression: toDate(CloseDate_sec_epoch)
Date Format: yyyy-MM-dd

(5) SAQL Expression: toDate(CloseDate,"yyyy-MM-ddTHH:mm:ss.000Z")
Date Format: yyyy-MM-ddTHH:mm:ss.000Z
this will cause error

(6) SAQL Expression: toDate(CloseDate,"yyyy-MM-dd")
Date Format: yyyy-MM-dd

Because there is no time in the source field, so all HH mm ss will be 00.


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

Side Note: can we refer a field in computeExpression within the same note?
Yes, as long as the field referred 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.








No comments:

Post a Comment

Page-level ad