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 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")
Date Format: yyyy-MM-dd
--> 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.








9 comments:

  1. This post saved my day!! appreciate tons!
    For those who might need - 'YYYY-MM-DD' is different than 'yyyy-MM-dd'. When I used all caps, it didn't work!!

    ReplyDelete
  2. how to add hours to UTC format, like how to add hours to now()

    ReplyDelete
  3. can we convert LastStageChangeDate which is in "yyyy-MM-ddTHH:mm:ss.000Z" to "yyyy-MM-dd"
    so the value should be yyyy-MM-dd:00:00:00

    ReplyDelete
  4. can we convert a date in "yyyy-MM-ddTHH:mm:ss.000Z" format to "MM/dd/yyyy" by keeping it as a date field?

    ReplyDelete
  5. Sort is not working properly when a converted date from2016-10-14T09:10:00.000Z to 10/14/2016 09:10 AM in the Einstein table

    ReplyDelete
    Replies
    1. if the result is date field in the dataset, sort should work in table

      Delete

Page-level ad