[resolved] 2 tables to 1 by tMap

Five Stars

[resolved] 2 tables to 1 by tMap

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
Moderator

Re: [resolved] 2 tables to 1 by tMap

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] 2 tables to 1 by tMap

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
Five Stars

Re: [resolved] 2 tables to 1 by tMap

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

Re: [resolved] 2 tables to 1 by tMap

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.
One Star

Re: [resolved] 2 tables to 1 by tMap

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

Re: [resolved] 2 tables to 1 by tMap

@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.
One Star

Re: [resolved] 2 tables to 1 by tMap

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

Re: [resolved] 2 tables to 1 by tMap

@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
One Star

Re: [resolved] 2 tables to 1 by tMap

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

Re: [resolved] 2 tables to 1 by tMap

@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?
One Star

Re: [resolved] 2 tables to 1 by tMap

Click on  on top of each flow in tMap to add the filters like this:
row1.ID1 != null
Five Stars

Re: [resolved] 2 tables to 1 by tMap

@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
Sixteen Stars

Re: [resolved] 2 tables to 1 by tMap

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.
Five Stars

Re: [resolved] 2 tables to 1 by tMap

@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
Sixteen Stars

Re: [resolved] 2 tables to 1 by tMap

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.
Sixteen Stars

Re: [resolved] 2 tables to 1 by tMap

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. 
Five Stars

Re: [resolved] 2 tables to 1 by tMap

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