1

Trying do do a basic ComputeExpression with a date type that picks one of 2 dates using a case statement.

Dataflow fails on the ComputeExpression step with this error message:

expression data type (Text) doesn't match specified type for field 'Billy'

Both fields being referenced are standard date fields in sf.com.

{
    "action":"computeExpression",
    "parameters":{
        "source":"Filter_Opportunities",
        "mergeWithSource":true,
        "computedFields":[
            {
                "name":"Billy",
                "format":"MM/dd/yyyy",
                "saqlExpression":"case when Close_Date_Adj__c is null then CloseDate else Close_Date_Adj__c end",
                "type":"Date"
            }
        ]
    }
}
  • 1
    Looks like wave related topic, you should add appropriate tags to get proper help. – Raul Feb 17 '17 at 12:50
1

Try surrounding the date fields with toDate(). Perhaps it's also necessary to enter a formatString, depending on the data.

{
    "action":"computeExpression",
    "parameters":{
        "source":"Filter_Opportunities",
        "mergeWithSource":true,
        "computedFields":[
            {
                "name":"Billy",
                "format":"MM/dd/yyyy",
                "saqlExpression":"case when Close_Date_Adj__c is null then toDate(CloseDate) else toDate(Close_Date_Adj__c) end",
                "type":"Date"
            }
        ]
    }
}

https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_eql.meta/bi_dev_guide_eql/bi_eql_functions_date.htm

  • I surrounded the fields with toDate() but they all errored with "root cause: invalid date value 2017-06-17". I added a formatstring to match the ComputeExpression Format and all records errored with the same root cause. – Matt Korlath Feb 17 '17 at 14:44
  • This is the new saqlExpression:"saqlExpression":"case when Close_Date_Adj__c is null then toDate(CloseDate,\"MM/dd/yyyy\") else toDate(Close_Date_Adj__c,\"MM/dd/yyyy\") end" – Matt Korlath Feb 17 '17 at 14:46
  • Have you tried yyyy/MM/dd as date format as well? – Folkert Feb 17 '17 at 15:10
  • Update the format but it still gave the sames root cause error – Matt Korlath Feb 17 '17 at 15:24
1

Had to play around with the date formats and "yyyy-MM-dd" worked.

{"name": "Billy",
"format": "yyyy-MM-dd",
"saqlExpression": "case when Close_Date_Adj__c is null then toDate(CloseDate,\"yyyy-MM-dd\") else toDate(Close_Date_Adj__c,\"yyyy-MM-dd\") end",
"type": "Date"
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.