Lookup function not returning any value

[zagahmadi]

Hi, 

I am attempting to use the lookup function to return a value within a table I am loading. The table is a list of securities where some have an underlying security identified with it. Since the underlying security is a security itself, I want to use the underlying security ID to lookup the name of the security where they key value is FKIDENDITYID. For example, the security name 'IBM_Bond' (fkidentityid = 21) has an underlying id = 41. The security name for fkidentityid = 41 is 'IBM'.  

However, the lookup shown below returns no data. I have tried it with and without the brackets in every combination. 

I am loading the table below via Oracle connection:

[STDB_GC_INSTRUMENT]:
LOAD
      [FKIDENTITYID],
      [FKUNDERLYINGID] AS [STDB_GC_INSTRUMENT.FKUNDERLYINGID],
      [NAME],
      Lookup('NAME','FKIDENTITYID',[FKUNDERLYINGID]) AS [STDB_GC_INSTRUMENT.UNDERLYINGNAME];
SELECT * FROM "2004"."STDB_GC_INSTRUMENT";

Any advice is greatly appreciated 

[Dalton_Ruer]

Doesn't appear that you have given the Lookup function a TableName since it appears you have field names that are common. 

Consider using ApplyMap instead so that you can clearly define the ID and TEXT relationships and it gives you the ability to define what value to use in the event you get an ID that doesn't have a name.