Talend compare date - unexpected result

One Star

Talend compare date - unexpected result

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 (unique match - left outer join):
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
Seventeen Stars

Re: Talend compare date - unexpected result

I don't know but you have the sources of all routines at your finger tips. Please open the Code node and open the routine (actually a java class) TalendDate. Here you find the code and you can learn the functionality or find out your possible misunderstanding.
Seven Stars

Re: Talend compare date - unexpected result

See my answer in your duplicate topic: http://www.talendforge.org/forum/viewtopic.php?id=29377
One Star

Re: Talend compare date - unexpected result

Hi Alevy,
Thank you for your reply.
Yes I'm using tmap and I have tried the All matches but then it gives me 6 records instead of 3 records,
SOURCE PRICE_NAME YEAR PRICE_DF VALID_FROM_DATE VALID_TO_DATE PIPELINE_SK FACILITY_NAME PIPELINE_NAME comparedate1 comparedate2
PRICE_FACT_MASTER AGL 240 PJ (2002) - CONTRACT PRICE -1 01-Jan-2008 01-Jan-2008 31-Dec-2010 16 UNKNOWN UNKNOWN -1 0
PRICE_FACT_MASTER AGL 240 PJ (2002) - CONTRACT PRICE -1 01-Jan-2008 0 UNKNOWN 1 1
PRICE_FACT_MASTER AGL 240 PJ (2002) - CONTRACT PRICE -1 01-Jan-2008 01-Jan-2008 31-Dec-2010 16 UNKNOWN UNKNOWN -1 0
PRICE_FACT_MASTER AGL 240 PJ (2002) - CONTRACT PRICE -1 01-Jan-2008 0 UNKNOWN 1 1
PRICE_FACT_MASTER AGL 240 PJ (2002) - CONTRACT PRICE -1 01-Jan-2008 01-Jan-2008 31-Dec-2010 16 UNKNOWN UNKNOWN -1 0
PRICE_FACT_MASTER AGL 240 PJ (2002) - CONTRACT PRICE -1 01-Jan-2008 0 UNKNOWN 1 1
I can have 3 records with the correct result if i use inner join but I need to use left outer join so I can set pipeline_sk to -1 if the data coming from source doesn't meet those condition. But I don't want the non match data from the lookup shows in my result.
Could you please advise what is the best way to achieve this? Please let me know if you require more information.
Thank you, appreciate it.
Rera


Re: Talend compare date doesnt return the right columnSince 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?
Last edited by alevy (Today 00:37:02)
Seven Stars

Re: Talend compare date - unexpected result

To be clear: you want to set pipeline_sk to -1 when all your conditions fail (source, name and dates) as you would get in SQL?
Unfortunately, Talend does not make that as easy as it should be. (I raised the seemingly pointless results it gives in this situation years ago and, while they didn't disagree, they haven't done anything about it either. Smiley Sad)
I think the only way you can do it is to add a unique identifier to each row1 and then follow tMap with a tUniqueRow keyed on that unique identifier and pipeline_sk. That will let through all matched pipeline_sks but only the first of the unmatched ones for each row1.
One Star

Re: Talend compare date - unexpected result

that's right but i only want data from the source, I don't really want data coming from lookup that doesn't match the condition.