Mapping and ApplyMap

Today I want to discuss how to use Mapping Tables in QlikView. 
Mapping Tables can be used in scenarios where code is used to get description. Mapping Tables should have only two columns.
In Qlikview it is always advisable to minimize the number of tables in the data model.

Suppose we've got two tables:

SalesTable:

OrderId, ProductId

1, 12

1, 13

3, 16

4, 22

and

ProductTable:

OrderId, ProductId

12, 12th

12, another one description

13, 13th

14, 14th

15, 15th

16, 16th

17, 17th

Load following script:

SalesTable:

Load *

Inline [

OrderId, ProductId

1, 12

1, 13 

3, 16

4, 22

]; 



Product_map:

mapping LOAD * INLINE [

ProductId, Description

12, 12th

12, another one description

13, 13th

14, 14th

15, 15th

16, 16th

17, 17th

];



SalesFact:

LOAD *,

     ApplyMap('Product_map', ProductId, 'No data') as ProductDescription

     Resident SalesTable;

     

// This table is longer needed in the FactTable

DROP Table SalesTable;

In fact Map in QlikView is a pair of <type, mapped_type> with 1:1 relationship.
To get information from map we have to use ApplyMap function.

The ApplyMap function is used for mapping any expression to a previously loaded mapping table. The syntax is:

applymap('mapname', expr [ , defaultexpr ] ) 
where:
mapname is the name of a mapping table that has previously been created through the mapping load or the mapping select statement. Its name 
must be enclosed by single, straight Quotation Marks in Scripting. 
expr is the expression, the result of which should be mapped. 
defaultexpr is an optional expression which will be used as a default mapping value if the mapping table does not contain a matching value for expr. If no default value is given, the value of expr will be returned as is. 

Take a look at Product_map closer.
1. There is no information about product with id=22 and we see No data message 

2. I intentionally duplicated information about product with id=12 in the map.

Despite of this ApplyMap returns only one (first) value.

Generally ApplyMap works like VLookup.

Note:
Keyword Resident means loading data from previously loaded table (SalesTable);
After loading data from SalesTable we may drop this table bacause SalesTable is longer needed in the FactTable;

Copyright © 2015 Ruslan Goncharov