Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Labels (3)
3 Replies
Anonymous
Not applicable
Author

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
Anonymous
Not applicable
Author

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.
Anonymous
Not applicable
Author

Hi,
Here is a reference about tMap on Talend Help Center.
Hope it will give you a little help
Best regards
Sabrina