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]

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. 

[Anonymous]

Thanks @rhall 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.      

[Anonymous]

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

[cterenzi]

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.

[Anonymous]

@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 🙂

[cterenzi]

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.

[Anonymous]

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.

[cterenzi]

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

[Anonymous]

Hi @rhall, @cterenzi

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

Output :

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 ?