Monday 16 September 2013

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

6 comments:

  1. Maybe more compact like this

    Exch_Rate_Map:
    Mapping
    Load
    Currency & '|' & Num(Date#(Month, 'YYYY-MM')) as [Curr 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];


    SalesTable:
    NoConcatenate
    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];

    ReplyDelete
  2. Like many things in QlikView, there are often multiple methods to achieve the same result. However, this post was to demonstrate the principal of dynamic applymap. There are many reasons why you might want to use dynamic applymap and the example is just that - an example to demonstrate the principal, not necessarily the way that I would implement the particular solution.

    ReplyDelete
  3. Nice Post Redmond....

    I believe, we don't need Temp_Curr table because FieldValueCount and FieldValue are return distinct values on the specified field name in those functions.

    For i=1 to FieldValueCount('Currency')
    LET vCurr = FieldValue('Currency',$(i)) ;
    Exch_Rate_Map_$(vCurr):
    Mapping
    Load Month, Rate
    Resident Monthly_Exchange_Rates
    Where Currency = '$(vCurr)';
    Next i

    ReplyDelete
  4. Does it work when the Currency field has a space in it? Like a human full name?

    ReplyDelete
  5. Hi Stephen, i'm tring your solution but when the script execution arrive at the applymap () qlik is freezing. I've tried to copy and paste your code on both qlikview and qliksense but the result is the same... qlik freezing.

    ReplyDelete

Note: only a member of this blog may post a comment.