topic One large mapping table or multiple apply maps? in QlikView

One large mapping table or multiple apply maps?

Mon, 26 Jan 2026 18:19:17 GMT

Hi there,

Hopefully someone can advise me on what works faster in regards to running the script. Is that:

One large mapping table (e.g. column A to Z) or
Multiple tables and use multiple times the ApplyMap function?

Thanks in advance for providing your experience on this topic.

René

Re: One large mapping table or multiple apply maps?

tresB — Fri, 17 Jan 2014 04:59:35 GMT

Not sure if understood right. To use applymap, the mapping table has to have two fields only, so , there i don't see a possibility of your "One large mapping table". You have to use separate mapping for individual fields. Isn't it so? Or, may be you meant something else.

Re: One large mapping table or multiple apply maps?

Anonymous — Fri, 17 Jan 2014 05:01:57 GMT

Read this link http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

Re: One large mapping table or multiple apply maps?

jagan — Fri, 17 Jan 2014 05:09:02 GMT

HI,

To use applymap, the mapping table has to have two fields only, and there should be only one mapping table for a Field, for example CountryID, for getting Country name there should should be mapping table all values in this. It is not good practice to use multiple tables for the Same CountryID. For every field you want to apply mapping there should be separate mapping table.

Hope it helps you.

Regards,

Jagan.

Re: One large mapping table or multiple apply maps?

Fri, 17 Jan 2014 05:40:02 GMT

Hi Tresesco,

I believe there are two options to apply mapping. I currently use 1 large mapping table (see below example) but since running the script is very time consuming I wonder if this is because of the fact that it is one large table. If I use the same data but want to use the ApplyMap function then for the same amount of data I need to create 7 tables.

The question is, what runs faster through the script: 1 big table or create 7 tables and use ApplyMap.

Thanks,

Re: One large mapping table or multiple apply maps?

rajeshvaswani77 — Fri, 17 Jan 2014 05:55:01 GMT

Hi Rene,

Both should be same, the mapping tables will get automatically dropped after the script execution is over.

thanks,

Rajesh Vaswani

Re: One large mapping table or multiple apply maps?

kiranmanoharrode — Fri, 17 Jan 2014 06:03:55 GMT

Dear Rene,

You can use Applymap() function when one to one mapping between 2 fields. but in a case of large mapping table you have to use either left join or right join as per your requirement.

If data volume is too huge then go for Large mapping Table for data filteration or multiple field mapping (many to Many mapping) with left or right join.

if there is one to one mapping between 2 fields then go for Applymap()

Regards

Kiran Rode

Re: One large mapping table or multiple apply maps?

tresB — Fri, 17 Jan 2014 06:08:55 GMT

Well, I assume that when you say one-large-table-applymap, you mean a JOIN. In that case, yes,

If one of the tables is large I would definitely prefer five Applymaps.

Also, there is always a risk with joining: Even if you think you have a one-to-one relationship between the tables so that no records will be duplicated in the join, you often in fact have a one-to-many situation so that the number of records increases. Which leads to incorrect calculations. I have seen this happen many times in real life. This is a risk you do not have with Applymap.

ApplyMap would be better. See, the quote above from Henric Cronström

Re: One large mapping table or multiple apply maps?

jonathandienst — Fri, 17 Jan 2014 06:08:57 GMT

The question is, what runs faster through the script: 1 big table or create 7 tables and use ApplyMap.

The most accurate way is to test both approaches and then make a decision. There are some differences between joining in one table vs multiple mappings with ApplyMap:

A single ApplyMap() is usually faster than a join.
A join could increase the number of rows in the table (if there are inter-table key fields that are not unique). ApplyMap will never increase the number of rows.
A join can map using more than one field. To do this using ApplyMap, you need to construct a compound key. This would remove some of the performance benefits of using ApplyMap.
An left outer join will result in nulls where the left table does not match anything in the right table (and vice versa). For ApplyMaps, you can supply a default value for unmapped rows.
The mapping tables are automatucally dropped at script end, so you dont need to do as much housekeeping.

HTH

Jonathan

Re: One large mapping table or multiple apply maps?

Fri, 17 Jan 2014 06:51:53 GMT

Thanks everyone for your reply.

Rgds,

René