Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
EdgarMM
Contributor III
Contributor III

ApplyMap is showing Null - Replace null with previous Month Non Null Value

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 
CountryDateKeyLocal_AmountExRateExRate expected KeyExRate
MX1/7/2020202001MX124518.7918.79 202001BR4.14
MX1/10/2020202001MX34918.7918.79 202001CO3316.85
CO1/15/2020202001CO39987433316.853316.85 202001MX18.79
BR1/19/2020202001BR582634.144.14 202002BR4.35
MX1/25/2020202001MX3284718.7918.79 202002CO3411.36
CO1/29/2020202001CO4893453316.853316.85 202002MX18.82
MX2/29/2020202002MX4729118.8218.82 202003BR4.89
MX2/29/2020202002MX4829418.8218.82 202003CO3905.33
CO2/29/2020202002CO73643411.363411.36 202003MX22.31
BR2/29/2020202002BR3753934.354.35   
MX2/29/2020202002MX537318.8218.82   
CO3/31/2020202003CO4592843905.333905.33   
MX3/31/2020202003MX438322.3122.31   
MX3/31/2020202003MX432922.3122.31   
CO3/31/2020202003CO248213905.333905.33   
BR3/31/2020202003BR37402044.894.89   
MX3/31/2020202003MX38438422.3122.31   
CO3/31/2020202003CO294823905.333905.33   
MX4/4/2020202004MX38430Null22.31   
CO4/4/2020202004CO5859204Null3905.33   
BR4/8/2020202004BR57383Null4.89   
MX4/12/2020202004MX48274Null22.31   

 

Thanks in advanced for your help.

Regards,

Edgar.

1 Solution

Accepted Solutions
Vegar
MVP
MVP

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

View solution in original post

2 Replies
Vegar
MVP
MVP

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

EdgarMM
Contributor III
Contributor III
Author

Thanks ¡¡ , It worked perfectly ¡¡

 

Regards,

Edgar.