topic Re: [resolved] 2 tables to 1 by tMap in Talend Studio

[resolved] 2 tables to 1 by tMap

Anonymous — Wed, 22 Mar 2017 06:58:12 GMT

Hi All, I have 3 tables: transaction, details, backup.
I would like to check:
IF (ID in transaction is equal to the ID in details)
the address in details table will be imported to the backup
ELSE
null;
How do I do that ?
My table is currently join together by transaction --> tMap --> Backup

Re: [resolved] 2 tables to 1 by tMap

Anonymous — Wed, 22 Mar 2017 07:59:15 GMT

Hi,
You can use ternary operator for if-else scenario in tMap or join your two tables.
The expression looks like: Condition?value if true:value if false
Best regards
Sabrina

Re: [resolved] 2 tables to 1 by tMap

TRF — Wed, 22 Mar 2017 08:03:50 GMT

Hi,
Use Transaction as the main flow for the tMap and Details as the lookup.
Join both tables in the tMap using the ID column and a Left Outer Join.
On Backup flow, populate all columns from Transaction but address from Details.

Regards,
TRF

Re: [resolved] 2 tables to 1 by tMap

Anonymous — Wed, 22 Mar 2017 08:45:17 GMT

Hello TRF, how do I do a left outer join?

Re: [resolved] 2 tables to 1 by tMap

Anonymous — Wed, 22 Mar 2017 08:51:31 GMT

Hello xdshi, I tried this method also but it didn't work.
I have 40 rows in transaction and 5998 rows in details. After I connect the transaction as main and details as lookup to the tmap, the output becomes 239920 rows. I wanted only 40 rows of data for the output. Please help.

Re: [resolved] 2 tables to 1 by tMap

TRF — Wed, 22 Mar 2017 08:51:43 GMT

This is the default. See the line "Join Model" on the capture.

Re: [resolved] 2 tables to 1 by tMap

Anonymous — Wed, 22 Mar 2017 08:58:07 GMT

@TRF, I have 40 rows in transaction and 5998 rows in details. After I connect the transaction as main and details as lookup to the tMap, the output becomes 239920 rows. I wanted only 40 rows of data for the output. Please help.

Re: [resolved] 2 tables to 1 by tMap

TRF — Wed, 22 Mar 2017 09:09:57 GMT

Drap and drop ID from Transaction to Details to establish the join or you'll get a cartesian product

Re: [resolved] 2 tables to 1 by tMap

Anonymous — Wed, 22 Mar 2017 09:25:37 GMT

@TRF, oh! However, I have ID1 and ID2 in Transaction and only ID in Details. How do I establish the join?
The backup table also have ID1 and ID2.
Details of ID1 and ID2 can be extracted from ID in details.
I tried adding row1.ID1 + row4.ID2 into the expression for ID.
However I receive this error message:
Exception in component tMap_1
java.lang.NullPointerException

Re: [resolved] 2 tables to 1 by tMap

TRF — Wed, 22 Mar 2017 09:42:46 GMT

Give an example (with sample data) to your data in and expected result

Re: [resolved] 2 tables to 1 by tMap

Anonymous — Wed, 22 Mar 2017 09:51:32 GMT

@TRF I think your solution works. It's just that my ID1 and ID2 have NULL values for some rows and thus the following error message appears:
Exception in component tMap_1
java.lang.NullPointerException
I'm putting row1.ID1 + row4.ID2 as the expression for ID to establish the join. How do I take care of the NULL value?

Re: [resolved] 2 tables to 1 by tMap

TRF — Wed, 22 Mar 2017 10:00:27 GMT

Click on on top of each flow in tMap to add the filters like this:

row1.ID1 != null

Re: [resolved] 2 tables to 1 by tMap

Anonymous — Thu, 23 Mar 2017 00:56:52 GMT

@TRF, I have tried adding filters where output.ID != null. However, received the following error message:
Detail Message: The operator != is undefined for the argument type(s) int, null

Example:
First transaction --> ID1: 123456 ID2: 789012
Second transaction --> ID1: 456789 ID2: 012345

Details table
123456           18-11-1980
789012           19-12-1990
456789           12-01-1985
012345           20-02-1975

Re: [resolved] 2 tables to 1 by tMap

Anonymous — Thu, 23 Mar 2017 01:02:37 GMT

Your column output.ID is of type int. This is a primitive type and therefore cannot be null. You will see in your table schema a tick box specifying that a column can be nullable. Tick that. It will change your column type from int to the class Integer. An object of class Integer can be null. This should enable the code suggested by TRF.

Re: [resolved] 2 tables to 1 by tMap

Anonymous — Thu, 23 Mar 2017 01:15:04 GMT

@rhall_2.0 Hello, I have tried to tick the nullable but a new error appears as follow:

Detail Message: The operator + is undefined for the argument type(s) null, Short

I currently have row1.ID1 + row4.ID2 in the expression for ID in tMap to prevent a Cartesian product for the join.
ID1 and ID2 is in type Short while ID is in type Integer.

I also activated filter at row1 : row1.ID1 != null + row1.ID2 != null
I activated filter at the output: row4.ID != null

Re: [resolved] 2 tables to 1 by tMap

Anonymous — Thu, 23 Mar 2017 01:32:02 GMT

OK. This seems to show that ticking the nullable tick box fixed that issue. This is a new one 🙂
Can you take a screenshot of your tMap with the column configurations in view? I would guess that your ID2 column is a short (also a primitive type) and also needs to have the nullable tick box ticked.

Re: [resolved] 2 tables to 1 by tMap

Anonymous — Thu, 23 Mar 2017 01:44:43 GMT

Actually, I've just re-read your error. Try this if my first suggestion doesn't work (....I don't think it will)....

(row1.ID1!=null ? row1.ID1 : 0) + (row4.ID2!=null ? row4.ID2 : 0)

The above will add the value of ID1 (if it is not null, or 0 if it is null) to the value of ID2 (if it is not null, or 0 if it is null). That should get round your issue.

Re: [resolved] 2 tables to 1 by tMap

Anonymous — Thu, 23 Mar 2017 02:20:07 GMT

@rhall_2.0, Hello. Thanks for the help. It works totally! Thanks for the solution!