topic Re: tfilterrow create and/or criteria that includes current date plus 30 days in Talend Studio

tfilterrow create and/or criteria that includes current date plus 30 days

Anonymous — Wed, 04 Dec 2019 22:33:51 GMT

I am trying to create using advanced mode criteria where status equals active or status equals inactive and the closing date is less than or equal to today plus 30 days. this is what I have but it's not working....suggestions?

(

(input_row.status.equals("active"))

(((input_row.status.equals("inactive")) && (input_row.closing_dt<=TalendDate.ADD_TO_DATE(TalendDate.getCurrentDate(), "DD", 30)))

)

Error says 'insert ")" to complete ArgumentList

Re: tfilterrow create and/or criteria that includes current date plus 30 days

Anonymous — Thu, 05 Dec 2019 02:35:26 GMT

If the data type of input_row.closing_dt is Date type, try this expression:
(input_row.status.equals("active"))||((input_row.status.equals("inactive")&&(TalendDate.compareDate(input_row.closing_dt,TalendDate.ADD_TO_DATE(TalendDate.getCurrentDate(), "DD", 30))!=1)))

Re: tfilterrow create and/or criteria that includes current date plus 30 days

Anonymous — Thu, 05 Dec 2019 17:36:47 GMT

Thanks Shong! yes Closed_dt is a date field. Although i'm not getting the error when running any longer and it's able to run the filter as you have it now is not including any records that are 'active'. at the least on the table there are 1885 records where status = 'active', should i nest the && statements?

Re: tfilterrow create and/or criteria that includes current date plus 30 days

Anonymous — Thu, 05 Dec 2019 18:24:27 GMT

FYI Nesting the && statements didn't work, thoughts?

Re: tfilterrow create and/or criteria that includes current date plus 30 days

Anonymous — Thu, 05 Dec 2019 18:25:56 GMT

I wanted to include this

Screen Shot 2019-12-05 at 9.33.10 AM.png

Re: tfilterrow create and/or criteria that includes current date plus 30 days

Anonymous — Fri, 06 Dec 2019 01:27:44 GMT

what is the filter expression you are using now?

BTW, please insert your image into the post instead of attaching it!

Re: tfilterrow create and/or criteria that includes current date plus 30 days

Anonymous — Fri, 06 Dec 2019 19:57:25 GMT

Yup sorry!

This is what I have now:

(input_row.STATUS.equals("active"))

(

((input_row.STATUS.equals("inactive")&&(TalendDate.compareDate(input_row.CLOSING_DT,TalendDate.ADD_TO_DATE(TalendDate.getCurrentDate(), "DD", 30))!=1)))

)

This image is the result i get when I run this subjob however in the table there are approx 1880+ 'active' records that should be going past the filter at the least.

Re: tfilterrow create and/or criteria that includes current date plus 30 days

Anonymous — Mon, 09 Dec 2019 18:36:23 GMT

I fixed syntax and it's running now however how it's not weeding out the inactive records that have a closing date greater than 30 days from today. records back with closing dates in 2012, 2013 are passing through the filter. Any suggestions are greatly appreciated!

Re: tfilterrow create and/or criteria that includes current date plus 30 days

Anonymous — Tue, 10 Dec 2019 05:28:56 GMT

If you using the expression
(TalendDate.compareDate(input_row.CLOSING_DT,TalendDate.ADD_TO_DATE(TalendDate.getCurrentDate(), "DD", 30))!=1)))
It will includes all old date.

What is the date range of CLOSING_DT you want to filter?
today<=CLOSING_DT<today+30Days？

Re: tfilterrow create and/or criteria that includes current date plus 30 days

Anonymous — Tue, 10 Dec 2019 18:31:05 GMT

Hi Shong, the second set of criteria should be STATUS = Inactive AND CLOSED_DT should be between TODAY and up to 30 days prior to today (so only loading inactive for records up to the last 30 days from today. ) THanks!

Re: tfilterrow create and/or criteria that includes current date plus 30 days

Anonymous — Wed, 11 Dec 2019 09:03:29 GMT

It's clear now, try this expression and let me know if it works.

(input_row.status.equals("active"))||((input_row.status.equals("inactive")&&(TalendDate.compareDate(input_row.closing_dt,TalendDate.addDate(TalendDate.getCurrentDate(), -30, "dd"))>=0)))

Re: tfilterrow create and/or criteria that includes current date plus 30 days

Anonymous — Wed, 11 Dec 2019 19:39:21 GMT

It looks like this worked, I don't have any inactive that are within the last 30 days however it's not bringing in any inactive records , Thanks!

Re: tfilterrow create and/or criteria that includes current date plus 30 days

Anonymous — Thu, 12 Dec 2019 01:59:12 GMT

Great, thanks for your feedback!