topic Re: Need help in filtering data based on time range in Talend Studio

Need help in filtering data based on time range

xx_emanis — Wed, 13 Feb 2019 14:56:36 GMT

Hi All,

I have a job where I am loading from Source table to target table and I need to apply certain filters while loading data to target table. Below is my job design.

Below is the filter logic based on which the target table should be loaded.

1. ORG_ID = SKP

2. DATE_COMPLETED or DATE_CREATED or SCHEDULED_COMPLETED_DATE is between

5:00:01 PM Day Prior TO 5:00:00 AM Current Day

Please help me on how to achieve this.

Thanks

Re: Need help in filtering data based on time range

akumar2301 — Wed, 13 Feb 2019 15:41:45 GMT

Hello , You could use tFilterRow/tMap to filter row .

tjava --OnSubjobOk --> inputfileComponent --main-->tfilterrow --main--> tlogrow

in tJava , define your start and end time and assign it global Var :

java.util.Date endtime = TalendDate.parseDate("yyyyMMddhhmmss", TalendDate.getDate("yyyyMMdd") + "050000");
java.util.Date starttime = TalendDate.addDate(TalendDate.parseDate("yyyyMMddhhmmss", TalendDate.getDate("yyyyMMdd") + "170000"), -1 , "dd");

globalMap.put("endtime", endtime);
globalMap.put("starttime", starttime);

in TfilterRow , select "use advance mode".

For above condition , you might have to use expression mentioned below :

input_row.ORG_ID.equals("SKP") &&
(

( TalendDate.compareDate(input_row.DATE_COMPLETED, (java.util.Date)globalMap.get("starttime")) >= 0
&& TalendDate.compareDate(input_row.DATE_COMPLETED, (java.util.Date)globalMap.get("endtime") ) < 0 )

( TalendDate.compareDate(input_row.DATE_CREATED, (java.util.Date)globalMap.get("starttime")) >= 0
&& TalendDate.compareDate(input_row.DATE_CREATED, (java.util.Date)globalMap.get("endtime") ) < 0 )

( TalendDate.compareDate(input_row.SCHEDULED_COMPLETED_DATE, (java.util.Date)globalMap.get("starttime")) >= 0
&& TalendDate.compareDate(input_row.SCHEDULED_COMPLETED_DATE, (java.util.Date)globalMap.get("endtime") ) < 0 )
)

Thats it.

Re: Need help in filtering data based on time range

xx_emanis — Thu, 14 Feb 2019 04:48:14 GMT

Thank you. I was able to achieve my requirement based on your solution.

Re: Need help in filtering data based on time range

Anonymous — Tue, 24 Sep 2019 16:14:05 GMT

hello,

same conditions in my scenario, after apply all this it gives Exception "java.lang.ClassCastException: java.lang.String cannot be cast to java.util.Date".

what can i do?

thanks,

Manish