I want to filter my records by last modified date and i want to bring only last 90 days of data to be brought in my data set. I dont want to use the compute node since compute process very costly. I want to achive this with filter transformation node itself. For example i tried using 'lastModifiedDate':EQ:LAST 90 DAYS or 'lastModifiedDate':EQ:LAST_90_DAYS. But both didn't work. Any help is much appreciated.
3 Answers
filter Expression Documentation does not suggest that relative date filters are supported. In fact, the filter transformation seems rather limited:
- Equals/IN:
EQ: - Not Equals/NOT IN:
N: - In range:
R:val0:val1 - Greater or equal:
R:val0 - Less or equal:
R::val0- note double colon:: - AND condition: join statements with comma
,
There is no OR support. And equals/not equals filters compare text arguments.
My advice: use computeExpression so you can use SAQL which supports relative date filters. I don't understand why you consider computeExpression too costly to use.
answered Dec 3, 2018 at 15:33
As you mentioned in your post it can be done using compute expression, since it may cost you the performance of the dataflow. You don't want to implement it that way.
My suggestion here would be create a formula field with checkbox return type. use the SOQL LAST_N_Days:90 function to check whether your record's last modified date lies in past 90 days or not.
Use a Filter transformation.
Uncheck the 'Use SAQL' checkbox.
Add the filter:
CreatedDate:EQ:Last_n_Days:90
Change 'CreatedDate' for the field you'd like to filter on
computeExpression"very costly"? There's no mention of any CPU limits in the following limits doc (except for maybe a timeout). The cost is either in the Dataflow or in the lens, and IMHO, Dataflow cost is worth making lenses simple for the User help.salesforce.com/articleView?id=bi_limits.htm&type=5