Obtaining a default value for a lookup when lookup not found (tMap)

One Star

Obtaining a default value for a lookup when lookup not found (tMap)

I have a scenario that, when a lookup fails in a tMap, I want to use a value from a "default" lookup. The default lookup is based on the same physical table as the main lookup, but is looked up with a static known business key - i.e. UNKNOWN.
What I have done to accomplish this is to add a duplicate lookup table (lookup based on same table) for each lookup table that I need a default value when the "main" lookup fails.
I got this to work via an expression in the output ==> ISNULL(MainLookupCustKey)?DefaultLookupcustKey:MainLookupcustKey)
However, my job is very cluttered due to all the "duplicate" lookup tables on the diagram.
Is there a better way to do this within the tMap (perhaps with an "alias" for the duplicate lookups) without having to use all the duplicate lookup tables in the job?
Dave
One Star

Re: Obtaining a default value for a lookup when lookup not found (tMap)

One way to accomplish this would be to read the value with a DB Input and a custom where clause to lookup "UNKNOWN" and save that in a global variable with tSetGlobalVar. So use: tDBInput->tSetGlobalVar (Replace DB with your database type)

Then you add logic in the tMap if the lookup returned null to replace with the value from the global variable.

e.g.

ISNULL(row2.MainLookupCustKey) ? (String)globalMap.get("myGlobalVariable") : row2.MainLookupCustKey


hth,

Thomas
One Star

Re: Obtaining a default value for a lookup when lookup not found (tMap)

One way to accomplish this would be to read the value with a DB Input and a custom where clause to lookup "UNKNOWN" and save that in a global variable with tSetGlobalVar. So use: tDBInput->tSetGlobalVar (Replace DB with your database type)
Then you add logic in the tMap if the lookup returned null to replace with the value from the global variable.
e.g.
ISNULL(row2.MainLookupCustKey) ? (String)globalMap.get("myGlobalVariable") : row2.MainLookupCustKey

hth,
Thomas

Thomas,
DB Input woud not be a good choice, as the lookup business key is multi-part - i.e. row1.SourceID + "UKNNOWN". In other words, row buy row, the SourceID could change. Hence, I need to look up the default row by row. I saw some "alias" functionality in the ELT version of tMap. Could that somehow be used to accomplish this, or does tMap support the "alias" functionality - cannot see that in the UI.
Dave