topic Re: [resolved] Filtering Date Ranges using Expression Builder in tMap in Talend Studio

[resolved] Filtering Date Ranges using Expression Builder in tMap

Anonymous — Sat, 16 Nov 2024 13:20:24 GMT

Hi All,
I have formulated a filtering date range (inclusive of given dates, date from and date to) inside expression builder in tMap and fortunately, it works!
But, is there any other way to do such date range filtering in TOS? What I have formulated looks like an old school for me

( TalendDate.parseDate("yyyy-MM-dd",TalendDate.formatDate("yyyy-MM-dd",row2.DateUpdated)).after(TalendDate.parseDate("yyyy-MM-dd",context.DateFrom)) || 
TalendDate.parseDate("yyyy-MM-dd",TalendDate.formatDate("yyyy-MM-dd",row2.DateUpdated)).equals(TalendDate.parseDate("yyyy-MM-dd",context.DateFrom)) ) &&
( TalendDate.parseDate("yyyy-MM-dd",TalendDate.formatDate("yyyy-MM-dd",row2.DateUpdated)).before(TalendDate.parseDate("yyyy-MM-dd",context.DateTo)) || 
TalendDate.parseDate("yyyy-MM-dd",TalendDate.formatDate("yyyy-MM-dd",row2.DateUpdated)).equals(TalendDate.parseDate("yyyy-MM-dd",context.DateTo)))

Regards,

Re: [resolved] Filtering Date Ranges using Expression Builder in tMap

Anonymous — Mon, 26 Jul 2010 09:55:42 GMT

ooohhh I just saw this post a while ago https://community.talend.com/t5/Design-and-Development/a-simple-test-on-Talendate-need-some-java-help/td-p/67599
Tnx again to Alevy

Re: [resolved] Filtering Date Ranges using Expression Builder in tMap

alevy — Tue, 27 Jul 2010 02:44:45 GMT

My pleasure
You might have already cleaned up your expression but why convert your row2.DateUpdated from a date to a string (formatDate) and then back to a date (parseDate)? Also, can you not define your context.DateFrom and context.DateTo as dates rather than strings so they do not need to be parsed to dates in your expression? That would leave you with:

!row2.DateUpdated.before(context.DateFrom) && !row2.DateUpdated.after(context.DateTo)

So much nicer

Re: [resolved] Filtering Date Ranges using Expression Builder in tMap

Anonymous — Tue, 27 Jul 2010 04:23:01 GMT

Hi Alevy,
Thanks for the suggestion, but I need to format the row2.DateUpdated in order to remove the time since it returns datetime data type.
For defining my context variables, the value of these will come from outside( property file) so this will be extracted as strings.
Regards,

Re: [resolved] Filtering Date Ranges using Expression Builder in tMap

Anonymous — Tue, 27 Jul 2010 04:28:12 GMT

Hi again Alevy,
Could you please explain why !before and !after are equal with <= and >= ? As I understand, ! = not or negation right? Still wondering

Re: [resolved] Filtering Date Ranges using Expression Builder in tMap

alevy — Tue, 27 Jul 2010 05:53:09 GMT

If a date is !before (i.e. not before), then it must be on or after (>= ). Similarly, if the date is !after then it is <=.
So if your row2.DateUpdated includes actual time values (not just always 00:00:00) then you still don't need to truncate it for the context.DateFrom comparison because it does not matter what time the update was on the test date - it will still be on or after 12am on the test date. You will, however, need to remove the time value for the context.DateTo comparison or updates on that date will be excluded. Alternatively, you could add a day to the context.DateTo when it's read in and then compare row2.DateUpdated.before(adjusted context.DateTo).
You can load date contexts from a file as long as (for now) they are in the format "yyyy-MM-dd HH:mm:ss" (see https://community.talend.com/t5/Archive/select-the-first-fille-in-a-folder/td-p/174462). Even if you have to read the dates as strings, I would suggest that you use a tJava to convert the strings to dates so that they do not need to be parsed repeatedly in tMap.

Re: [resolved] Filtering Date Ranges using Expression Builder in tMap

Anonymous — Tue, 27 Jul 2010 05:59:40 GMT

You enlighted us once again Tnx Alevy

Re: [resolved] Filtering Date Ranges using Expression Builder in tMap

alevy — Wed, 28 Jul 2010 01:11:44 GMT

You can load date contexts from a file as long as (for now) they are in the format "yyyy-MM-dd HH:mm:ss" (see bug).

Just to clarify: the above was incorrect as I misunderstood how loading date context values is supposed to work. In fact, the pattern to apply by tContextLoad in parsing the date string is defined with the context variable's default value. The string passed to tContextLoad should be just the date in the defined format (not including the pattern itself).