Seven Stars

Detect reason of unmapped record with tMap

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.

 

unmapped_reason.PNG

 

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.

 

 

  • Data Integration
Tags (2)
1 ACCEPTED SOLUTION

Accepted Solutions
Seven Stars

Re: Detect reason of unmapped record with tMap

Finally, I got the workaround :

 

Finally.PNG

 

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_2_0 and @cterenzi . The solution is possible just because of your co-operation guys. Cheers Smiley Happy

 

 

19 REPLIES
Ten Stars

Re: Detect reason of unmapped record with tMap

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. 

Rilhia Solutions
Seven Stars

Re: Detect reason of unmapped record with tMap

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.      

Ten Stars

Re: Detect reason of unmapped record with tMap

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

Rilhia Solutions
Ten Stars

Re: Detect reason of unmapped record with tMap

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.

Ten Stars

Re: Detect reason of unmapped record with tMap

@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 :-)

Rilhia Solutions
Ten Stars

Re: Detect reason of unmapped record with tMap

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.

Ten Stars

Re: Detect reason of unmapped record with tMap

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.

Rilhia Solutions
Ten Stars

Re: Detect reason of unmapped record with tMap

Agreed, I'd rather keep all my rows together and split them out with explicit conditions rather than rely on catching inner join misses.
Seven Stars

Re: Detect reason of unmapped record with tMap

Hi @rhall_2_0@cterenzi

 

With the help of your suggestions, I'm able to use tMap variable. Please have a look below sample case :

 

TestTables.PNG

 

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"
	) 
)

 

 

Output :

 

UnmapReason.PNG

 

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 ?

 

 

Ten Stars

Re: Detect reason of unmapped record with tMap

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.

Rilhia Solutions
Seven Stars

Re: Detect reason of unmapped record with tMap

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.

 

Ten Stars

Re: Detect reason of unmapped record with tMap

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.

Ten Stars

Re: Detect reason of unmapped record with tMap

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.

Rilhia Solutions
Seven Stars

Re: Detect reason of unmapped record with tMap

@cterenzi

 

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

null.PNG

 

2. For warehouse as first lookup and then vendor

null2.PNG

Seven Stars

Re: Detect reason of unmapped record with tMap

@rhall_2_0, Will outer join provide expected result ?

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

Re: Detect reason of unmapped record with tMap

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.

Seven Stars

Re: Detect reason of unmapped record with tMap

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

Ten Stars

Re: Detect reason of unmapped record with tMap

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)

Rilhia Solutions
Seven Stars

Re: Detect reason of unmapped record with tMap

Finally, I got the workaround :

 

Finally.PNG

 

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_2_0 and @cterenzi . The solution is possible just because of your co-operation guys. Cheers Smiley Happy