Detect reason of unmapped record with tMap

[Anonymous]

Hi,

I'm using tMap for lookup in 2 tables using Inner Join with match model Unique Match. Please have a look in below diagram.

As you can see in above tMap editor screenshot, vendor and warehouse are lookup tables.

1. If I found match i.e.for both vendor and warehouse - The records will go to 'mapped'

2. If I didn't get a match i.e. for either of vendor or warehouse - The record will go to 'unmapped'

- Here I'm unable to get the exact reason behind unmapping. Following are the cases :

Reason 1) Both vendor and warehouse not matched in lookup

Reason 2) Only vendor matched, but not warehouse

Reason 3) Only warehouse matched, but not vendor

How to detect the reason from above 3 for unmatch ?

UPDATE :

 I've one more column added at the end of unmapped output table named 'UNMAP_REASON' wherein I've to insert the text describing why it is not mapped. The reson must be only one from above 3.

Is there naything I can do with tMap Variables ?

Thank you.

[Anonymous]

The thing that jumps to mind is that both of your lookups are joined via "ERP_WEST". The Vendor and Warehouse ID 456 will both be matched if they are both joining on the same column of the same dataset. I may have misunderstood, but this would be where I would start looking.

[Anonymous]

I found the reason behind why the WarehouseID = 456 not mapped :

First, we take VendorID = 456 from main table (3rd row) and looking up in vendor table for its CODE. But the VendorID 456 not exists, so the CODE it returned is null. Now, it will never check or lookup WarehouseID = 456 is existing in warehouse or not. since it is already catched in unmapped. As a result, CODE return from warehouse is also null. Hence I'm getting UnmapReason as 'vendor and warehouse not matched' in unmapped output.

I'm able to check this scenario by changing the sequence of lookup. warehouse first and then vendor. Result is different.

[cterenzi]

vendor.CODE == null ? (warehouse.CODE == null ? "vendor and warehouse not matched" :"vendor not matched") :
		(warehouse.CODE == null ? "warehouse not matched" : "Unknown") 

I revised your code a bit.  This is what @rhall suggested, and while subtly different from what you used, will handle all cases, irrespective of the order.

[Anonymous]

You should be able to resolve this (the first or second lookup table checked) by setting them both to be using an outer join. This will force both joins to be tested.

[Anonymous]

@cterenzi

I've tried @rhall 's expression after my expression was fail. But it was also giving me the same result.

Next, I replaced tMap variable expression with : 

vendor.CODE  + " - " + warehouse.CODE 

The output :

1. For vendor as first lookup and then warehouse

2. For warehouse as first lookup and then vendor

[Anonymous]

@rhall, Will outer join provide expected result ?

I want to insert mapped record in one table, and unmapped records (with reason) in another table.

[cterenzi]

I went back and tested my sample job and found the same results.  It seems the underlying code doesn't check all join paths if an earlier lookup is unmatched.  You will have to use an outer join.

[Anonymous]

Yes @cterenzi, exactly. But I don't think it will provide expected result. Anyway, checking with outer join.  

[Anonymous]

With the outer join method you will find it easier using tMap filters. See the input expression filters on this link if you are unsure (https://www.talendbyexample.com/talend-tmap-component-joins.html)

[Anonymous · Accepted solution]

Finally, I got the workaround :

1. Changed join model to Left outer join

2. Used expression filter in output 'mapped'

3. Used tMap variable with conditional expression I used before.

Thanks a lot @rhall and @cterenzi . The solution is possible just because of your co-operation guys. Cheers