topic Re: Join two tables in Talend Studio

Join two tables

_AnonymousUser — Sat, 16 Nov 2024 12:53:53 GMT

Hello, and thanks for you time to anyone kind enough to help me.
I have two SQL tables like these:
year | month | quantity1 year | month | quantity2
---------------------------- ----------------------------
2010 | 01 | 5 2010 | 02 | 7
2010 | 06 | 10 2010 | 07 | 6
2010 | 10 | 8 2010 | 10 | 10
and I need a single table like this:
year | month | quantity1 | quantity2
------------------------------------------
2010 | 01 | 5 | 0
2010 | 02 | 0 | 7
2010 | 06 | 10 | 0
2010 | 07 | 0 | 6
2010 | 10 | 8 | 10
I tried tMap and tUnite but I can't find a way. Can you give me a hand? Thanks

Re: Join two tables

Anonymous — Sat, 28 May 2011 17:38:29 GMT

Hello,
tMap should do the trick, but you need to
1) Set the join criteria to full outer join
2) calculate the keys in the output table (you can use variables in tMap) in a way that if t1.year_id is null then you take t2.year_id (same for month)
3) make sure you tag as nullable year_id and month_id in t1 and t2
Should be enough
Ah, for point 2) you need to use the syntax (t1.year_id == null) ? t2.year_id : t1.year_id

Hope it helps
Bye
Francesco

Re: Join two tables

Anonymous — Sat, 28 May 2011 17:57:00 GMT

uhm... to bad I just noticed there is no full outer join option in tMap, so you need to do it with a tunite followed by an aggregation or perform the full outer join in your RDBMS.
So, back to the tUnite option.
I used your data and created a job as visible in the attached pic.
The two tmasps are just used to created a zero column q2 in the first table and a zero column q1 in the second one

Re: Join two tables

_AnonymousUser — Sun, 29 May 2011 08:54:34 GMT

I really appreciate your help. By the way I found a different way to solve this problem (even if it took a good amount of gray matter )
It involves two simmetrical JOINs: left and right. In the screenshot I posted below you can see this method applied to 4 tables in order to have this kind of result:
year | month | qty1 | qty2 | qty3 | qty4
---------------------------------------------
2010| 01 | 5 | 10 | 0 | 0
2010| 02 | 15 | 0 | 5 | 0
2010| 03 | 0 | 4 | 8 | 5
2010| 04 | 7 | 12 | 4 | 7
2010| 05 | 5 | 16 | 0 | 3
2010| 06 | 0 | 6 | 9 | 0
2010| 07 | 5 | 11 | 0 | 2
...et cetera. Obvioulsy the starting tables are all in the form . Anyhow your method may be simpler so I'll be using both for my purpose.

(sorry for the imageshack post, but I can't find the upload image function)

Re: Join two tables

Anonymous — Sun, 29 May 2011 13:12:16 GMT

Hello,
did not understand your solution and the screenshot is tiny, cannot read anything out of it
The image upload function is below the text box when you post a reply, it shows "image upload" and a combo with the number of slots for the upload.
Anyhow, I am glad you found a solution
Note : from the tiny screenshot you posted it seems you are using mysql tables (or anyway a rdbms).
If so, you could use a ELT approach (use tELTmySQLInput / tELTMap ) . There you have the "full louter join" option.
This would make your job much simpler (provided all the tables are in the same server).
Bye
Francesco

Re: Join two tables

Anonymous — Sun, 29 May 2011 15:43:32 GMT

You can solve the problem avoiding joins.
Just unite the two flows in a tUnite ( but before that remember to add a "logical" additional qty field in the source schemas defaulting to zero, in order to have two input flows with two quantity fields, one always defaulting to zero ).
Then just use taggregate to sum grouping by year and month.

Re: Join two tables

_AnonymousUser — Sun, 29 May 2011 22:13:27 GMT

Yes, I found this same solution (the one right above this post) too today, but I didn't use any tUnite, I just used 4 queries (I had 2 more tables) and 3 UNION. Of course in every query there was one attribute field with the real data and 3 with "0 AS abcd" and for each query i shifted the data field right one place.
In TOS I just used a tAggregateRow to group by the month field and I was done.
Thanks anyway for your help, and if I can ask another question I just want to know how to do this:
Attr1 | Attr2
------+------ AAA | BBB | CCC
AAA | 123 ====> ------+-----+-----
BBB | 456 123 | 456 | 789
CCC | 789
or else to just multiply the first column of a table for the first value af Attr2 (123), the second column for the second value (456), the third column for the third value, and so on.
PS. I can't find the uplad image button. Does it apper only if you are a registered user?
Bye

Re: Join two tables

alevy — Fri, 03 Jun 2011 03:10:14 GMT

To upload image don't use the Quick Post box at the bottom of the topic, instead click the Post Reply link at the right above that box.