[resolved] Filtering Date Ranges using Expression Builder in tMap

One Star

[resolved] Filtering Date Ranges using Expression Builder in tMap

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! Smiley Happy
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 Smiley Happy
( 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,

Accepted Solutions
Seven Stars

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

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 bug). 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.

All Replies
One Star

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

ooohhh I just saw this post a while ago Smiley Happy http://www.talendforge.org/forum/viewtopic.php?id=11644
Tnx again to Alevy Smiley Wink
Seven Stars

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

My pleasure Smiley Happy
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 Smiley Wink
One Star

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

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. Smiley Happy
For defining my context variables, the value of these will come from outside( property file) so this will be extracted as strings.
Regards,
One Star

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

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

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

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 bug). 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.
One Star

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

You enlighted us once again Smiley Happy Tnx Alevy Smiley Happy
Seven Stars

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

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).