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

[Anonymous]

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

[_AnonymousUser]

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

[Anonymous]

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