Applymap does not return the correct field value

[43918084]

I have below applymap code that I want to return the 2nd field "FXFO".  But the result return the first field in of the mapping table.  May I know what I have done wrong?

[FXFO]:
Mapping
LOAD
	"Asset/Liab Indicator" & '-' & "Trade ID" as  AL_deal#, 
    "Type We Pay/Receive" as FXFO
  
FROM [lib://test/CAO_MPMSWBTP_HK_20191231_141815.xlsm]
(ooxml, embedded labels, table is CAO_MPMSWBTP_HK_20191231_141815);

[Table]
LOAD
 iF(left("acct",1)='1','A', if(left("acct",1)='2','L','na')) &'-'& "Dealno)" as AL_deal#, 
 
applymap('FXFO',iF(left("acct1)='1','A', if(left("acct",1)='2','L','na')) &'-'& "Dealno") as FXFO,
  
FROM [lib://test/SAL LY.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

[ashishkalia]

Hi,

I can see one syntax error in the ApplyMap 
applymap('FXFO',iF(left("acct1)='1','A', if(left("acct",1)='2','L','na')) &'-'& "Dealno"))

Pl check. 

[43918084]

I apologize it is my typo.  The original script does not have that error.

[lorenzoconforti]

Can you post the actual script you are using? There are other typos in the section you've posted

"Dealno)"

[Table] (colon missing)

as FXFO,

in your resulting Table, do you get what you expect in the field  AL_deal#? Do you actually get anything in the field FXFO?

[43918084]

I Apologize for the mistake as I face some difficulties pasting the information here.  Thank you very much for your patience and guidance

Please find below the original table

[FXFO]:
Mapping
LOAD
"Asset/Liab Indicator" & '-' & "Trade ID" as  AL_deal#, 
    "Type We Pay/Receive" as FXFO
FROM [lib://test/CAO_MPMSWBTP_HK_20191231_141815.xlsm]
(ooxml, embedded labels, table is CAO_MPMSWBTP_HK_20191231_141815);

[LYCM]:
LOAD
  iF(left("Nominal AC",1)='1','A', if(left("Nominal AC",1)='2','L','na')) &'-'& "DealNo" as AL_deal#, 
 applymap('FXFO',iF(left("Nominal AC",1)='1','A', if(left("Nominal AC",1)='2','L','na')) &'-'& "DealNo") as FXFO,
"Nominal AC",
"Product Owner",
"DealNO",
 "PARENT ACRONYM "
FROM [lib://test/SAL LY.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

[lorenzoconforti]

It's returning the key you are using for the mapping (i.e.  the second argument in the applymap function), not the first field in the mapping table; it's happening as it's not finding the mapping matches. There is something wrong either with your mapping table or your main table

Just run this and check the two tables to make sure the AL_deal# fields actually match

[FXFO]:
//Mapping
LOAD
"Asset/Liab Indicator" & '-' & "Trade ID" as  AL_deal#, 
    "Type We Pay/Receive" as FXFO
FROM [lib://test/CAO_MPMSWBTP_HK_20191231_141815.xlsm]
(ooxml, embedded labels, table is CAO_MPMSWBTP_HK_20191231_141815);

[LYCM]:
LOAD
  iF(left("Nominal AC",1)='1','A', if(left("Nominal AC",1)='2','L','na')) &'-'& "DealNo" as AL_deal#, 
 //applymap('FXFO',iF(left("Nominal AC",1)='1','A', if(left("Nominal AC",1)='2','L','na')) &'-'& "DealNo") as FXFO,
"Nominal AC",
"Product Owner",
"DealNO",
 "PARENT ACRONYM "
FROM [lib://test/SAL LY.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);