Need help in filtering data based on time range

[xx_emanis]

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

[akumar2301 · Accepted solution]

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. 

[xx_emanis]

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

[Anonymous]

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