Qlik Community

Ask a Question

QlikView Administration

Discussion Board for collaboration on QlikView Management.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
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
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

2 Replies
Vegar
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

EdgarMM
Contributor III
Contributor III
Author

Thanks ¡¡ , It worked perfectly ¡¡

 

Regards,

Edgar.