Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there:
I hope you can help me on this; I have two tables, the first one contains the expenses amounts and the second one the exchange rates per year and month by country; I created a key to relate both tables and using ApplyMap to asign the corresponding exchange rate for each row in my first table, the problem I'm facing now is in the current month because I have not jet the information for the exchange rate and in these cases I want to get the exchange rate for the previous month and Country.
ApplyMap('T_Ex_Rate_MAP', Key,) as EXRATE
Table: Expenses | Table: T_Ex_Rate | |||||||
Country | Date | Key | Local_Amount | ExRate | ExRate expected | Key | ExRate | |
MX | 1/7/2020 | 202001MX | 1245 | 18.79 | 18.79 | 202001BR | 4.14 | |
MX | 1/10/2020 | 202001MX | 349 | 18.79 | 18.79 | 202001CO | 3316.85 | |
CO | 1/15/2020 | 202001CO | 3998743 | 3316.85 | 3316.85 | 202001MX | 18.79 | |
BR | 1/19/2020 | 202001BR | 58263 | 4.14 | 4.14 | 202002BR | 4.35 | |
MX | 1/25/2020 | 202001MX | 32847 | 18.79 | 18.79 | 202002CO | 3411.36 | |
CO | 1/29/2020 | 202001CO | 489345 | 3316.85 | 3316.85 | 202002MX | 18.82 | |
MX | 2/29/2020 | 202002MX | 47291 | 18.82 | 18.82 | 202003BR | 4.89 | |
MX | 2/29/2020 | 202002MX | 48294 | 18.82 | 18.82 | 202003CO | 3905.33 | |
CO | 2/29/2020 | 202002CO | 7364 | 3411.36 | 3411.36 | 202003MX | 22.31 | |
BR | 2/29/2020 | 202002BR | 375393 | 4.35 | 4.35 | |||
MX | 2/29/2020 | 202002MX | 5373 | 18.82 | 18.82 | |||
CO | 3/31/2020 | 202003CO | 459284 | 3905.33 | 3905.33 | |||
MX | 3/31/2020 | 202003MX | 4383 | 22.31 | 22.31 | |||
MX | 3/31/2020 | 202003MX | 4329 | 22.31 | 22.31 | |||
CO | 3/31/2020 | 202003CO | 24821 | 3905.33 | 3905.33 | |||
BR | 3/31/2020 | 202003BR | 3740204 | 4.89 | 4.89 | |||
MX | 3/31/2020 | 202003MX | 384384 | 22.31 | 22.31 | |||
CO | 3/31/2020 | 202003CO | 29482 | 3905.33 | 3905.33 | |||
MX | 4/4/2020 | 202004MX | 38430 | Null | 22.31 | |||
CO | 4/4/2020 | 202004CO | 5859204 | Null | 3905.33 | |||
BR | 4/8/2020 | 202004BR | 57383 | Null | 4.89 | |||
MX | 4/12/2020 | 202004MX | 48274 | Null | 22.31 |
Thanks in advanced for your help.
Regards,
Edgar.
Either you manipulate your mapping table in such a way that your create rows with current month rates based on last month's rates.
Or you manipulate your ApplyMap expression in some way. Maybe you can use the alt function.
Alt(ApplyMap('T_Ex_Rate_MAP', Key, null()), ApplyMap('T_Ex_Rate_MAP', KeyValueForPreviousMonth ,null()))
Either you manipulate your mapping table in such a way that your create rows with current month rates based on last month's rates.
Or you manipulate your ApplyMap expression in some way. Maybe you can use the alt function.
Alt(ApplyMap('T_Ex_Rate_MAP', Key, null()), ApplyMap('T_Ex_Rate_MAP', KeyValueForPreviousMonth ,null()))
Thanks ¡¡ , It worked perfectly ¡¡
Regards,
Edgar.