topic Talend compare date doesnt return the right column in Talend Studio

Talend compare date doesnt return the right column

_AnonymousUser — Wed, 01 May 2013 01:04:01 GMT

Hi,
I'm trying to get the pipeline_sk when a date is fall between the valid_from_date and valid_to_date and match it by 2 columns, but I dont have the right result.
row1:
SOURCE FACILITY_NAME PRICE_DATE PRICE_DF
PRICE_FACT_MASTER UNKNOWN 20080101 01-Jan-2008
PRICE_FACT_MASTER UNKNOWN 20080101 01-Jan-2008
PRICE_FACT_MASTER UNKNOWN 20080101 01-Jan-2008
row2:
PIPELINE_SK SOURCE PIPELINE_NAME VALID_FROM_DATE VALID_TO_DATE CURRENT_INDICATOR RECORD_VERSION RECORD_STATUS
16 PRICE_FACT_MASTER UNKNOWN 01-01-2008 31-12-2010 0 1 0
17 PRICE_FACT_MASTER UNKNOWN 01-01-2011 01-01-9999 1 2 1

The condition are below:
row1.Source = row2.source
row1.facility_name = row2.pipeline_name
TalendDate.compareDate(row1.PRICE_DF,row2.VALID_TO_DATE,"yyyy-MM-dd") <= 0 &&
TalendDate.compareDate(row1.PRICE_DF, row2.VALID_FROM_DATE,"yyyy-MM-dd") >= 0
I'm expecting to get pipeline_sk = 16 as a result, but its not. can you please advise?
Regards,
Rera

Re: Talend compare date doesnt return the right column

alevy — Thu, 02 May 2013 00:35:50 GMT

Since you don't explain how you're trying to do this and haven't provided a screen-print, I've assumed you're using a tMap.
Have you set the "Match Model" to "All matches"? The Match Model affects the results from the join fields only (i.e. row1.Source = row2.source && row1.facility_name = row2.pipeline_name) and only after that is the filter expression (TalendDate.compareDate(row1.PRICE_DF,row2.VALID_TO_DATE,"yyyy-MM-dd") <= 0 && TalendDate.compareDate(row1.PRICE_DF, row2.VALID_FROM_DATE,"yyyy-MM-dd") >= 0) applied. With "Unique match", only the last match (pipeline_sk = 17) is accepted to be tested by date, which it fails, resulting in no records in your result.
If that's not your problem, perhaps you're parsing the dates from your sources incorrectly?

Re: Talend compare date doesnt return the right column

janhess — Thu, 02 May 2013 09:14:50 GMT

Could it be because the dates are in a different format?