Join and compare dates

One Star

Join and compare dates

I have two tables in two databases:

T1:
      date1
      data1

T2
     date2
     data2

Now I want to compare the two dates ( TalendDate.compareDate(row1.date_maj,  row2.DATE, "dd-MM-yyyy HH:mm:ss")  )
If the resultat is -1 I get data1 and if the result 1 I get data2

Now the question is how to get data2 if there is no row in T1
and data1 if there is no data in T2

Any ideas???


Ten Stars

Re: Join and compare dates

With tMap components you have two types of input; "Main" and "Lookup". There must be 1 and can only be 1 "Main" input and it drives the component. The "Lookup" inputs can be 0 or many and are what they say on the tin...lookups. If you have no data in your "Main" input, then you will get no data out. 

To get data out if there is no T1 input (I am assuming "Main"), you can't. To get data out if there is no T2 input (I am assuming "Lookup"), you need to change the "Join Model" to be a left outer join. 

To solve your problem it sounds like you may want to use another computed input as your "Main" input. The logic you have described is missing something. What is the key that links the dates to be compared? From the screenshot it looks like "numero_cde and numero_ligne". Can you have a "Main" input that just supplies those (maybe even the same one you are using now) and then join in the two inputs we are talking about here as "Lookups"? Then you can join to the lookups using outer joins and can then deal with the situation you have described above. If T1 is missing, you can use the T2 lookup value, if T2 is missing, you can use the T1 lookup value. 
One Star

Re: Join and compare dates

Yes in my example T1 & T2 are both "Main" inputs.

The two tables are updated by two applications in two databases
After to consolidate data, you need to know which has the last updated data.

So you can have 3 cases:
- data in T1 and nothing in T2
- data in T2 and nothing T2
- data in T1 and T2 and in this case you need to compare the dates
               * T1 can be the last updated table
               * T2 can be the last updated table
               * T1 & T2 can be updated at the same time

Of course to compare the dates I have a key in the tables.
ex:
                    Order N°
                    Order line N°
                    last update date
So I must compare the dates for the the same keys in the two tables.

How to implement it with Talend?
Is it posssible to manage the 3 cases in one talend job??

Didier
Ten Stars

Re: Join and compare dates

They can't be both "Main" inputs. A tMap only accepts one "Main". The rest are "Lookups". Take a look at the links and you will see.
One Star

Re: Join and compare dates

Sorry but which links???
Ten Stars

Re: Join and compare dates

The lines joining your components to the tMap. They are named and will have "(Main)" and "(Lookup)" next to the names. By default the names are "row#"

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog