[resolved] How to filter date ranges in tMap/tFilter using context?

One Star

[resolved] How to filter date ranges in tMap/tFilter using context?

I'm using TIS 3.0.4, extracting data from Postgres and moving it to MySQL.
I need to check the last change date in the input table and extract only records with a last_change_dtm within the number of days specified in a context. The context is defined as a integer, and set to 14.
I can do this in a query using "WHERE "table"."last_change_dtm" >= current_date - "+context.Days_History+".

How would I accomplish this in a tMap or tFilter? I don't know Java.
Thanks,
Celeste

Accepted Solutions
Community Manager

Re: [resolved] How to filter date ranges in tMap/tFilter using context?

Hello
Ok, I show you a job, i filter rows on tFilterRow component.
in.csv:
1;2009-07-12
2;2009-09-25
3;2009-08-29
4;2009-09-24
filter condition on tFilterRow:
input_row.date.getTime()>TalendDate.addDate(TalendDate.getCurrentDate(),-context.Days_History,"dd").getTime()

TalendDate.getCurrentDate() ==>get the current date.
TalendDate.addDate()==>rollback a date or add a date, -5 means rollback 5 days.
result:
Starting job forum8290 at 14:53 25/09/2009.
.--+----------.
| tLogRow_1 |
|=-+---------=|
|id|date |
|=-+---------=|
|2 |2009-09-25|
|4 |2009-09-24|
'--+----------'
Job forum8290 ended at 14:53 25/09/2009.
----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies
Community Manager

Re: [resolved] How to filter date ranges in tMap/tFilter using context?

Hello Celeste
On the input flow of tMap, add an expression, see my screenshot.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] How to filter date ranges in tMap/tFilter using context?

Hi Shong,
The context.Days_History is an integer. What I want to do is extract all records from the source database where the last_change_dtm (date field) is greater than today - XX days (current_date - context.Days_History).

Thanks,
Celeste
Community Manager

Re: [resolved] How to filter date ranges in tMap/tFilter using context?

Hello
Ok, I show you a job, i filter rows on tFilterRow component.
in.csv:
1;2009-07-12
2;2009-09-25
3;2009-08-29
4;2009-09-24
filter condition on tFilterRow:
input_row.date.getTime()>TalendDate.addDate(TalendDate.getCurrentDate(),-context.Days_History,"dd").getTime()

TalendDate.getCurrentDate() ==>get the current date.
TalendDate.addDate()==>rollback a date or add a date, -5 means rollback 5 days.
result:
Starting job forum8290 at 14:53 25/09/2009.
.--+----------.
| tLogRow_1 |
|=-+---------=|
|id|date |
|=-+---------=|
|2 |2009-09-25|
|4 |2009-09-24|
'--+----------'
Job forum8290 ended at 14:53 25/09/2009.
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] How to filter date ranges in tMap/tFilter using context?

Thanks, Shong!
This looks like exactly what I need. I find I don't have this function in version 3.0.4 - waiting for Support ticket open 9/17 to get download for latest TIS version. Then I should be all set.
Celeste