topic Re: Applymap does not return the correct field value in App Development

Applymap does not return the correct field value

43918084 — Mon, 20 Dec 2021 21:32:42 GMT

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

Re: Applymap does not return the correct field value

ashishkalia — Tue, 24 Mar 2020 07:37:53 GMT

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.

Re: Applymap does not return the correct field value

43918084 — Tue, 24 Mar 2020 14:31:38 GMT

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

Re: Applymap does not return the correct field value

lorenzoconforti — Tue, 24 Mar 2020 15:27:20 GMT

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?

Re: Applymap does not return the correct field value

43918084 — Tue, 24 Mar 2020 18:24:27 GMT

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

Re: Applymap does not return the correct field value

lorenzoconforti — Tue, 24 Mar 2020 22:38:54 GMT

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