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 ?
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 ?
Solved! Go to Solution.
You are almost there. You just need to carry out a check on the values returned by the where Vendor and Warehouse tables. I'm assuming that IDs are not null for these tables. So you could carry out checks for null IDs. If the ID for Vendor is null, then Vendor is missing. If the ID for Warehouse is null, then the Warehouse is missing.
Thanks @rhall_2_0 for your quick reply.
Could you please explain your solution. I didn't get it.
Please have a look at my update in question. It is what I'm looking for.
OK, look at the pseudo code below (pseudo code because it is an approximation of what you will require)....
vendor.ID==null ? (warehouse.ID==null ? "Vendor and Warehouse not found" : "Vendor not found") : (warehouse.ID == null ? "Warehouse not found" : "")
I added brackets to make it easier to read. This is an inline IF. It essentially says.....
If vendor.ID is null AND warehouse.ID is null, then "Vendor and Warehouse not found"
If vendor.ID is null AND warehouse.ID is not null, then "Vendor not found"
If vendor.ID is not null AND warehouse.ID is null, then "Warehouse not found"
Else, empty String
I'd switch both lookups to a left join. Then you can add a second tMap with four outputs, each with an expression filter handling one of the cases (match both, match neither, match 1 or match 2)
OR you can do it all in one tMap using variables. Set up a variable for each lookup table, with an expression like vendor.ID, then create an output for each match case with a filter expression that checks if those variables are null:
1) Var.var1 != null && Var.var2 != null 2) Var.var1 == null && Var.var2 != null 3) Var.var1 != null && Var.var2 == null 4) Var.var1 == null && Var.var2 == null
You can put the UNMAP_REASON value right in each output.
Edit: I thought because no values came through from either lookup in an Inner Join Reject output that you couldn't test for either in an expression, but it seems you can. The inline conditional is a better solution.
@cterenzi spotted something I missed. You will need to set them to be outer joins as he suggests. I missed this because it is kind of autopilot for me to work in this way. Cterenzi deserves the solution for this one.....and I need to read the problem in more detail :-)
You don't, actually. Inner join reject will catch any records that don't match either lookup, but the UNMAP_REASON expression can still reference the lookup values and see what matched and what didn't. I didn't think this was possible initially, but it looks like it works.
I didn't think that would work either @cterenzi. Good to know, but I think using outer joins gives you more control in this scenario.
With the help of your suggestions, I'm able to use tMap variable. Please have a look below sample case :
Expression used in tMap variable :
vendor.CODE == null && warehouse.CODE == null ? "vendor and warehouse not matched" : ( vendor.CODE == null ? "vendor not matched" : ( warehouse.CODE == null ? "warehouse not matched" : "Unknown" ) )
Everything is just as expected, except in 2nd row (ID=3) of Unmapped output, the reason is 'vendor and warehouse unmatched' instead of 'vendor not matched'. What is going wrong ? Am I missing something in tMap variable expression ?
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.
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.
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_2_0 suggested, and while subtly different from what you used, will handle all cases, irrespective of the order.
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.
I've tried @rhall_2_0 '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
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.
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)