topic Re: multiple joins in single tJoin in Talend Studio

multiple joins in single tJoin

Anonymous — Tue, 09 May 2017 16:34:01 GMT

I want to apply 2 joins in a tMap component. first one is inner join and second one is left outer join. However i am unable to do so. Someone suggested me to use tJoin reject for left outer join. However, logically speaking rejected of inner join is everything that isn't inner join, which is combination of left outer join and right outer join. Can anyone help?

Re: multiple joins in single tJoin

cterenzi — Tue, 09 May 2017 16:44:23 GMT

Could you do a left join for both fields and then filter out null values resulting from the join you wanted to be inner?

Edit: To elaborate, tMap doesn't support inner and outer joins to the same lookup source, and it doesn't sound like tJoin will do what you're looking for either.

Re: multiple joins in single tJoin

Anonymous — Tue, 09 May 2017 16:54:45 GMT

Is there any workaround or alternative solution for this problem?

Re: multiple joins in single tJoin

cterenzi — Tue, 09 May 2017 17:36:58 GMT

You could do a left join on both fields and include the key value from the lookup feed in the output, then filter records where the inner join lookup key is null.

Re: multiple joins in single tJoin

vapukov — Tue, 09 May 2017 22:54:32 GMT

other way is use staging database (even local SQLite) - and use full power of SQL

time is money and sometime addition step make screen much more cleaner, code much more readable, and final result for less time.

Re: multiple joins in single tJoin

Anonymous — Thu, 11 May 2017 13:02:21 GMT

Not entirely sure what you meant. Can you elaborate a little further?

Re: multiple joins in single tJoin

cterenzi — Thu, 11 May 2017 13:48:15 GMT

Load the data into a database and then extract it with a sql statement that performs the join you want. If you don't have a persistent database available and can't set one up locally, the tHSQLDb components have a "HSQLDb In Memory" option which will create a temporary database that is torn down at the end of the job.

You would attach your input and lookup flows to separate tHSQLDbOutput components, creating two tables, then use tHSQLDbInput to join them and retrieve the values you need.

Re: multiple joins in single tJoin

Anonymous — Thu, 11 May 2017 13:57:49 GMT

Even more confused. Sorry, can you suggest what you meant by "You could do a left join on both fields and include the key value from the lookup feed in the output, then filter records where the inner join lookup key is null."? New answer is somewhat different from original answer. I got a feeling that your original comment has potential. Thanks!

Re: multiple joins in single tJoin

vapukov — Thu, 11 May 2017 22:30:01 GMT

@Enthusiast wrote:

Not entirely sure what you meant. Can you elaborate a little further?

Talend support JOINs, but functionality is limited.

Often - fasted and more flexible way - make this in database

SQL support JOINS, lookups and other conditions in single complicated query. So if You experienced in databases - this is allow You achieve expected result faster and code could be more readable.

Also - Talend very effective work with in memory operations, but when data bigger than memory - it time to problems. Opposite - databases oriented for work with data much more bigger than RAM.

Re: multiple joins in single tJoin

Anonymous — Fri, 12 May 2017 03:36:44 GMT

I am required to do this job in a windows server without any database. I am reading all the data form multiple CSV and JSON files. Asking for a database to the company management is not an option for me. I will need to find any workaround within Talend to be able to do it. Thanks though.

Re: multiple joins in single tJoin

vapukov — Fri, 12 May 2017 07:35:11 GMT

up to You of course,

but for use SQLite for example - do not need request any permissions from management 🙂 (very often used way with Python ETL scripts)

Re: multiple joins in single tJoin

cterenzi — Fri, 12 May 2017 14:01:19 GMT

@Enthusiast wrote:

Even more confused. Sorry, can you suggest what you meant by "You could do a left join on both fields and include the key value from the lookup feed in the output, then filter records where the inner join lookup key is null."? New answer is somewhat different from original answer. I got a feeling that your original comment has potential. Thanks!

Use a tMap to join your main flow to your lookup flow. Join on both key columns, and set the join type to Left Outer Join. Add a field to your output schema containing the key from the lookup flow that you wanted to be an inner join. Any records generated where the lookup key is null you want to filter out. You can filter those records a number of ways. A tFilterRow component is an obvious choice, but you can also add an expression filter to your tMap output.

If this is still confusing, you should probably attach some screenshots of your job and your tMap configuration so we can give more specific advice.