Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
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
];
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];
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
];
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
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
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
Hi @Aspiring_Developer !, i made the inlines just because there is no another sample of data
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 ?