topic Applymap using keys in App Development

Applymap using keys

Aspiring_Developer — Fri, 27 Nov 2020 13:28:34 GMT

Hello Everyone,

Can anyone please help me with the below scenario:-

Table A

Year	Entity Reporting Currency	Exchange Rate
2021	ARS	10
2021	USD	50
2021	EUR	70

Table B

Year	Entity Code	Entity name	Country	Entity Reporting Currency	Amount	New AmountAs per the currency (to be calculated)
2021	ARS	201	USA	USA	480
2021	USD	226	Europe	EUR	490
2021	EUR	501	Argentina	ARS	500

I want to calculate the new amount as per the currency rate from table A

I tried using Apply map by creating a key in table A as mapping load Year & entity reporting currency as key, exchange rate from table A

Not sure how can we implement apply in table B to calculate the new amount as per exchange rate from table A

Can anyone please help ?

Thank You

Re: Applymap using keys

QFabian — Fri, 27 Nov 2020 13:58:42 GMT

Hi @Aspiring_Developer , please try this :

[Table A]:
Load * INLINE [
Year, Entity Reporting Currency, Exchange Rate
2021, ARS, 10
2021, USD, 50
2021, EUR, 70];

MapTable:
mapping
Load
Year & ' | ' & [Entity Reporting Currency] as YEarEntity,
[Exchange Rate]
Resident [Table A];

[Table B]:
Load * INLINE [
Year, Entity Code, Entity name, Country, Entity Reporting Currency, Amount
2021, ARS, 201, USA, USA, 480
2021, USD, 226, Europe, EUR, 490
2021, EUR, 501, Argentina, ARS, 500 ];

NewTable:
Load
*,
Amount * ApplyMap('MapTable', Year & ' | ' & [Entity Code]) as NewAmount
Resident [Table B];
drop table [Table A];
drop table [Table B];

Re: Applymap using keys

lironbaram — Fri, 27 Nov 2020 14:05:33 GMT

Hi ,

use this script it will give you the result you look for.

ratemap: mapping load Year&'_'&"Entity Reporting Currency" as key, "Exchange Rate" as exRate; load * inline [ Year, Entity Reporting Currency, Exchange Rate 2021, ARS, 10 2021, USD, 50 2021, EUR, 70 ]; Table2: load *, applymap('ratemap', Year&'_'&"Entity Reporting Currency") * Amount as NewAmount; load * inline [ Year, Entity Code, Entity name, Country, Entity Reporting Currency, Amount 2021, ARS, 201, USA, USD, 480 2021, USD, 226, Europe, EUR, 490 2021, EUR, 501, Argentina, ARS, 500 ];

Re: Applymap using keys

PrashantSangle — Sat, 28 Nov 2020 14:49:56 GMT

another approach than applymap is you can achieve this in front end also. Just create composite key in back end.

and in front end (designer window)

add expression > amount * exchange rate

Regards,

Prashant Sangle

Re: Applymap using keys

Aspiring_Developer — Wed, 02 Dec 2020 11:36:17 GMT

Hi, Thank you for your response

Can we achieve it without taking inline table as we have exchange rate for different rates 2019,2020,2021 .

Thanks in advance

Re: Applymap using keys

Aspiring_Developer — Wed, 02 Dec 2020 12:18:36 GMT

Hello,

The above one worked, however, there is one currency which is not available in mapping table (ex EUR), but it is present in data table.

Since, we have used apply map, it should not return any value as we do not have EUR exchange rate in the file.

I tried this

"Amount in Entity Reporting Currency" / ApplyMap('Map_table', "Year" & ' | ' & "Entity Reporting Currency",'NA') as NewAmount

But still it is populating incorrect values instead of "NA".

Please help

Re: Applymap using keys

QFabian — Wed, 02 Dec 2020 12:48:08 GMT

Hi @Aspiring_Developer !, i made the inlines just because there is no another sample of data

Re: Applymap using keys

Aspiring_Developer — Wed, 02 Dec 2020 13:08:36 GMT

hello,

thank you. Yes i did the same by loading the excel file.

However, it giving me some incorrect values as i do not have the exchange rate for EUR in mapping file, but i have the data for EUR in data file. so it should not return any value

Can you please help ?