Apply map or Join

[D19PAL]

Spoiler

Hi

Is it fair to say applymap is better than joins?

Is it worth doing 12 applymaps from table 2, or just join them? Left join table 2 to table 1.

Thanks 

[marcus_sommer]

IMO are mappings more suitable in the most scenarios. They are very fast especially compared against joins even if there are multiple mappings needed. Beside this mappings are much more flexible and have no risks to impact the number of records if there are any missing and/or duplicated key-values. Also you could directly calculate with the mapping-result - by a join you will always need another resident-load to access the new values. 

Further mappings could be nested in various way, for example:

• containing multiple lookup-values within a hierarchically and sorted structure
• return-values could be string-concatenated like value1 & '|' & value2 and the calling applymap() is wrapped with a subfield() to fetch the wanted return sub-value (in your case it could mean that maybe a single mapping is enough)
• applymap() contains a third-parameter - the default value - which means the applymap() could be nested multiple times 

My usage of joins is therefore nearly completely limited to scenarios in which I use inner/outer joins to filter datasets or to create ones but not to add values from one table to another.

- Marcus

[JordyWegman]

I share the opinion of @marcus_sommer  with the exception when loading large files. Sometimes it's better to load a large file once and join it instead of loading it twelve times. Yes this could be done by the subfield trick explained by Marcus but requires a bit more creative thinking (it's a more advanced trick). 

Jordy

Climber