tJoin usage

One Star

tJoin usage

Hello,
First of all I would like to thank the Talend developers for the excellent product.
I am quite new to the product and I have a question regarding the tJoin component usage. Can you please point me to a tJoin example or some description of that component? I?m trying to join data from a delimited file with data from a database look up table which has more than 4 million records. However, all records get rejected.
Thank you in advance
Michael
Community Manager

Re: tJoin usage

Hi
From the screenshot of your job, I think you should know the usage of this component.
However, all records get rejected.

1) Make sure you have some same value of 'SecurityId' and 'id_imnt_alt'.
2)Change the column order of output schema. should be :
column:
SecurityId
SecurityExc...
ADT
ADTCurrency
Country
id_imnt
Because Talend mapping the input schema to output schema in order by default.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tJoin usage

Hi Shong,
Thanks for the reply. I made sure that 'SecurityId' and 'id_imnt_alt' have the same value and I moved the id_imnt column at the last position in the column list. It didn?t work for me. Please consider a similar scenario for tMap with detail information on data and queries, where id_imnt_alt=SecurityId=?AN8068571086?:
1. tFileInoputDelimeted_1 connected to tLogRow displays the following:
Starting job lookup_test at 10:59 24/01/2008.
connecting to socket on port 3578
connected
connecting to socket on port 4547
connected
AN8068571086|TEST|1234|TST|TS
...

2. This is what a select on the lookup table returns for a given id_imnt_alt ( 'AN8068571086'):
select id_imnt, id_imnt_alt from EXCHANGE_IMNT_ALTERNATE_ID where id_imnt_alt='AN8068571086' and id_typ_alt_imnt = 'I'

id_imnt id_imnt_alt
===================
123 AN8068571086
123456 AN8068571086
3.lookup query returns two records as it is displayed by tLogRow_1
123|AN8068571086
123456|AN8068571086
4. When I try to join these records via tMap, all of them get rejected because of the lookup inner join.
...
tLogRow_3] AN8068571086|TEST|1234|TEST|TS||0
...
For some reason tMap cannot join two records in this scenario.
Can you please advise if I am doing anything wrong and what should I do in order to join the data of the two input components?
Thanks
Michael
Employee

Re: tJoin usage

I think you should trim your data in your DB query, in particular from your table "test_table".
I suspect that your column "id_imnt_alt" has DB type CHAR(25), so tMap can't match your data.
One Star

Re: tJoin usage

yes, i've already got it. Thanks. For my better understanding of Talend - the look up field in Talend (id_imnt_alt ) has always had column size equal to 12. The same size was defined for data in the file. However it seems that Talend didn't check the size of coming data from a database for a particular column against the size of the destination field in Talend. Is it correct?
Community Manager

Re: tJoin usage

Hi
However it seems that Talend didn't check the size of coming data from a database for a particular column against the size of the destination field in Talend. Is it correct?

Yes, there is no check in the component by default.
There is a tSchemaComplianceCheck component who can check the length or other..
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tJoin usage

Hi,
I am new to Talend tool, can somebody provide me step by step example for tJoin by connecting the database and extract the data from two tables using tJoin.
Regards
Babu
Moderator

Re: tJoin usage

Hi babu,
Here are related scenarios in component reference:TalendHelpCenter:tJoin.
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.