Joining multiple tables to single table

Four Stars

Joining multiple tables to single table

Hi,

 

I'm trying to join a couple of database tables to a single table. It pretty much needs to do the following: For every row in 'klantorderregel' table with 'gereed == true' a row needs to be created in 'leveropdracht' containing information from 'klantorderregel', 'klant' and 'product'. However, when I run the job it creates 12 new rows in 'leveropdracht' while there are only 4 that need to be made. Can anyone help me? Here are some screenshots:  

 

2019-05-27 (3).png2019-05-27.png

Eight Stars

Re: Joining multiple tables to single table

Hi @sjhdonge ,

 

What is the datatype of the column 'gereed'? If it is int, can you please try changing the filter condition on 'klantorderregel' data set(input) to row1.gereed==1 ?

Four Stars

Re: Joining multiple tables to single table

It's boolean. If I remove the expression it will output 24 rows, so I suppose the expression is correct. I think it has something to do with no expr. key existing between klantorderregel and klant. It must actually somehow see what 'klant' (translated customer) is connected to a 'klantorderregel' without the existing column in klantorderregel.

Four Stars

Re: Joining multiple tables to single table

 
Eight Stars

Re: Joining multiple tables to single table

@sjhdonge ,

 

okay, so then your filter expression is right.

I see that you are making an inner join, but there are no fields from dataset1 that is pulled to dataset2. marked in red below. So the join is not actually happening.

screenshot-12.png

 

 

Four Stars

Re: Joining multiple tables to single table

Yeah I see. Is the only way to solve this by making a new column in dataset 1 and link it to a column from dataset 2?

Eight Stars

Re: Joining multiple tables to single table

@sjhdonge ,

 

no you don't have to make a new column. columns from the 1st dataset that you are using to join it with dataaset2 should be dragged to the corresponding column in dataset2.Screen Shot 2019-05-27 at 10.55.01 AM.png

in the above screenshot im trying to join 2 datasets on Project_Id column and so I have Project_Id from row1 against Project_Id in row2 dataset and im making a left outer join here.

please take a look at this link if it is not clear
https://www.talend.com/resources/joining-two-data-sources-with-tmap-component/

 

Four Stars

Re: Joining multiple tables to single table

I'll take a look at it tonight

2019 GARTNER 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

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