One large mapping table or multiple apply maps?

[#1]

Hi there,

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

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

Thanks in advance for providing your experience on this topic.

René

[tresesco]

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.

[Anonymous]

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

[jagan]

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.

[#5]

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,

[rajeshvaswani77]

Hi Rene,

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

thanks,

Rajesh Vaswani

[kiranmanoharrode]

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

[tresesco]

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

[jonathandienst]

Hi

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

[#10]

Thanks everyone for your reply.

Rgds,

René