0

I'm trying to create a computeExpression in my Dataflow to calculate: how many days have passed since a given date until now and add this calculation as a new field into the dataset.

This is what I'm trying to do:

"ComExp_UpToDate": {
    "action": "computeExpression",
    "parameters": {
      "source": "My_Last_Node",
      "mergeWithSource": true,
      "computedFields": [
        {
          "defaultValue": "0",
          "name": "u2d_Field",
          "saqlExpression": "daysBetween(​toDate(namespace__CustomField_Date__c, \"yyyy-MM-dd\"​), now())",
          "label": "Days until now",
          "type": "Numeric",
          "precision": 16,
          "scale": 0
        }
      ]
    }
  },

I'm getting the following error: Error executing node ComExp_UpToDate: invalid field expression daysBetween(​toDate(namespace__CustomField_Date__c, "yyyy-MM-dd"​), now()) for field 'u2d_Field': Error at position 14: Unexpected character 'â' (0Bw58000000DIavCAG_0Bq58000000LAlECAW)

I've tried a few other options and all failed:

  • Different date formats: "yyyy-M-d"
  • Using _Year, _Month, _Day dimensions, but they're not created until registering the dataset
  • I have also created a computeExpression to transform this Date field into a Text field to check the date format, and it's showing "yyyy-MM-dd" but maybe internally it's different.

Notes:

  • namespace__CustomField_Date__c is a Date field
  • I don't think the solution should be to create a formula field in the Custom Object
0

I've just figured out the problem.

The SAQL expression is fine so if anyone wants to use it do not copy and paste, this was my mistake in the first place.

The SAQL Expression in the question has some zero-width spaces. I wasn't able to see this char using Sublime Text nor Notepad++ so I had to go back to my roots and open the file using vi editor. Zero-Width Spaces

| improve this answer | |

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.