Applymap using keys

[Aspiring_Developer]

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

[QFabian · Accepted solution]

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

[lironbaram · Accepted solution]

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

[PrashantSangle]

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

[Aspiring_Developer]

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

[Aspiring_Developer]

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

[QFabian]

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

[Aspiring_Developer]

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 ?