ApplyMap

I’m going to start of this blog with one of my first Qlik romances, the ApplyMap(). I feel in love with this function right from the start when introduced to QlikView. It might not be the fastest tool in my toolbox, but it sure is simple to use and implement and it is also valid in Qlik Sense as all other QlikView script syntax

The common use

My most common use case for ApplyMap() is to use it to get values from a lookup table. It’s an useful alternative for adding single field value to the data model without adding data model complexity.

I want to add a ProductGroupID to my Sales table and not keep it in my product table. School books and DBA may get at thrill from snowflake structures, but QlikView does not. The more references between tables the more effort it takes to calculate stuff in front end. I want to keep my data model as narrow as possible - not breaking my start scheme.

To do this I will create a map table (lookup table) with productID and ProductGroupID to expand my Sales dimension with the later. To accomplish this I use ApplyMap().

Syntax

Map table

• The map table is created by using MAPPING LOAD instead of the normal LOAD.
• The map table may only have two columns.

Map_Prod_ProdGrp: MAPPING LOAD ProductID, ProductGroupID FROM Product.qvd (qvd);

• The first column is used as mapping key, the second the value you want to return.
• The map table should have a defined name, hence it is used as a parameter in the ApplyMap() function.
• The field names in a mapping table are not of interest for the ApplyMap() function.

ApplyMap()

• ApplyMap() is used when loading tables inside the QlikView and Qlik Sense script.

ApplyMap(‘map_table_name’, MappingValue, [DefaultValue]) AS Dim

• The 1^th parameter in ApplyMap() is the map table to be used inside single quotes (').
• The 2^nd parameter in ApplyMap() is the value that is to be looked up and mapped.
• The 3^rd parameter in ApplyMap() is the optional default return value.
• It is returned if ther lookup value does not exsist in the map table.
• If it is not set the lookup value will be returned

Performance issues

I mentioned earlier that ApplyMap() is not the fastest tool in the toolbox. In a large data tables an ApplyMap() will slow down my load. It will break my optimized load. So it might not be the function to use if you are chasing load speed. For the most I don’t mind the speed, in normal sized datasets it is never an issue and the benefits are greater.

If you are chasing load speed then you can try to keep the lookup table as a part of the datamodel or using Join instead of ApplyMap(). They could be the faster solutions, but I often choose ApplyMap() over these two.

Applymap for trimming my data model

The more snowflaking I put into my data model the more internal references my QlikView application have to handle when calculating in the front end application. A rule of thumb is to keep the data model as a star scheme. The orange tables above are part of my star, but the gray ProductGroup is breaking my star.

By moving the ProductGroupID down to my Sales table I manage to keep my data model as a tight and tidy star scheme. The result is a shorter calculation path for my expressions and thereby a faster application for my end users.

Applymaps strengths compared to join

The ApplyMap() I will always return the first matching value from a map table. It will never generate additional rows of data. I always feel safe on that aspect, duplicating data, with ApplyMap().

On the contrary there is Join. I find Join often to be faster than ApplyMap() to run, but with a big risk adding extra rows of data into my tables if I don’t have total control on my joining data table. ApplyMap() is also faster to implement when I don’t have perfect distinct data.

If you are unfamiliar with the Join in QlikView concept I recommend Matt Fryers blogpost where he is explaining joins.

Using left join on Sales-table with the data in the picture above I will duplicate all Sales transactions with ProductID = 2. With ApplyMap() I will map on a single value on each row in sales, but with no risk of adding extra rows.

Summary

I often use ApplyMap(). It is a easy way to complement my data model with an extra data field based on a lookup value. It breaks my optimized loads, but as long I’m not chasing a reduced load time I tend to use it over an extended data model or joins. Its concept is easy to learn and fast to implement and I never have to worry about duplicate data as I do when joining data fields into my data models.

Furhter reading: Join and Lookups - community.qlik.com/docs/DOC-3412