Dynamic ApplyMap

I use ApplyMap all the time in QlikView scripts.  It is one of the best ways of getting data from a lookup table - especially with the facility to specify a default value.

I use it so much that I often forget about one quite useful aspect of it - the name of the mapping table is provided as a text value.  Usually this means that I am passing a literal string, but it can also mean that I can pass a dynamically calculated string - use a different map for different values.

In the scenario below, we have a currency table with a value per month.  Often the approach might be to join this table to the fact table and then do a second load to calculate the base value (or even not bother and handle that in the front-end).

You could, of course, just concatenate a number of fields to create a key for mapping, but I prefer a different and, I think, more elegant method.

In this this example, I load the currency table into multiple mapping tables and then dynamically pick the right mapping table while loading the fact table:

Monthly_Exchange_Rates:
Load 
Currency, 
Num(Date#(Month, 'YYYY-MM')) As Month,
Rate
Inline [
Currency, Month, Rate
EUR, 2013-05, 1
EUR, 2013-07, 1
EUR, 2013-06, 1
EUR, 2013-09, 1
EUR, 2013-08, 1
GBP, 2013-09, 0.842711
GBP, 2013-05, 0.848677
GBP, 2013-06, 0.851957
GBP, 2013-08, 0.858893
GBP, 2013-07, 0.862144
USD, 2013-05, 1.297799
USD, 2013-07, 1.307939
USD, 2013-06, 1.317866
USD, 2013-09, 1.323692
USD, 2013-08, 1.331518
CNY, 2013-05, 7.972392
CNY, 2013-07, 8.027845
CNY, 2013-06, 8.088211
CNY, 2013-09, 8.100795
CNY, 2013-08, 8.151601
];

Temp_Curr:
Load Distinct Currency as Currency_List
Resident Monthly_Exchange_Rates;

For i=0 to FieldValueCount('Currency_List')-1
Let vCurr=Peek('Currency_List', $(i), 'Temp_Curr');

Exch_Rate_Map_$(vCurr):
Mapping
Load Month, Rate
Resident Monthly_Exchange_Rates
Where Currency = '$(vCurr)';

Next

Drop Tables Temp_Curr, Monthly_Exchange_Rates;


SalesTable:
Load
*,
Sales_Local/ExRate as Sales_Base;
Load
Date,
Country,
Currency,
Sales as Sales_Local,
ApplyMap(
'Exch_Rate_Map_' & Currency, 
Floor(MonthStart(Date)), 

1) as ExRate

Inline [

Date, Country, Currency, Sales
2013-05-02, Ireland, EUR, 123
2013-05-13, Ireland, EUR, 322
2013-06-11, France, EUR, 343
2013-07-02, USA, USD, 343
2013-08-12, UK, GBP, 233
2013-08-30, China, CNY, 223
2013-09-01, UK, GBP, 543
2013-09-24, USA, USD, 412
];

It's common enough to come across a scenario like this, so I hope you find it useful.

---

Stephen Redmond is author of QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond