Multiple tables with different schemas to one table

One Star

Multiple tables with different schemas to one table

I have searched the forum and I haven't found a solution the following scenario.
I have 3 or more tables with a lot of overlapping columns but the actual schema is different for each one.
One of the three tables might contain new rows, so left join won't work I need something more like a union.
With an output with all the columns and I also want set some column update priority, something like
row1.status != null? row1.status : row2.status != null? row2.status : row3.status
for example
customer_csv
id,fist_name, status
1,"john", "active"
2,"mike", "inactive"
3,"bryan", "active"
customer_api
id, status
1, "inactive"
3, "active"
4, "inactive"

customer_xml
id, name, phone
3,"bryan", "888-888-000"

Output
customer
id, name, status, phone
1,"john", "inactive",null
2,"mike", "inactive",null
3,"bryan", "active", "888-888-000"
4,null, "inactive", null

I have tried many different combinations with tMap but nothing really has worked, any ideas how it can be done?
BTW I am using TIS Version: 5.1.2

Re: Multiple tables with different schemas to one table

try thinking about as SQL left joins
the wording that you have used to define your requirement is wrong.
could you please clarify your experience (sql , app developer)?
UNION datasets will give you a different result.

that is what tMap is for.

connect into tMap the following:
customer_csv
is used as the main input

customer_api
as a lookup
then in tMap use the ID column to join the data sets
and add the column you want to the output
One Star

Re: Multiple tables with different schemas to one table

Thanks for your reply, I am Java developer with very little experience working with ETL tools.
You are right, I need something more like Full Outer Join, since all three tables might have new records.
If I use left outer join the customer id = 4 will be left out because is not on the customer_csv table.
Moderator

Re: Multiple tables with different schemas to one table

Hi,
Here is a reference about tMap on Talend Help Center.
Hope it will give you a little help
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.