A LEFT JOIN will result in a table consisting only of rows where the linking field values are represented in the first table. So if there is a unique value in the second table, it will not appear in the output table.
APPLYMAP will add a new field. If no match is found, you can put a default value but the field is still created. For instance, if you are trying to add a phone number field to a customer, if no match is found you still create the field and can insert a value to represent 'no phone number found'.
JOINs are very powerful as you can add many fields at once but not very efficient. Both reduce optimization but JOINS can cause serious delays if they are big datasets.
I just had a scenario in one of my scripts where I had a LEFT JOIN and in effect it did change the nr. of records, even with a LEFT JOIN. I used an Applymap instead and it works. It's also a lot more performant to add just one field, no?
I wonder - now I have a very similar scenario - currently the LEFT JOIN does not alter the nr. of records, but in future, who knows - but now I have to add three fields, so I would need three mapping_tables. Is Applymap still the better option here? Probably, no?
If used before join it specifies that a left join should be used. The resulting table will only contain combinations of field values from the raw data tables where the linking field values are represented in the first table. If used before keep, it specifies that the second raw data table should be reduced to its common intersection with the first table, before being stored in QlikView.
The ApplyMap script function is used for mapping the output of an expression to a previously loaded mapping table
I almost never use joins if I can avoid it. Applymap is in most cases a better option. So I would probably use use Applymap also in your case.
A Left join removes records from table 2 that don’t have a match in table 1. But the "Left" doesn’t change the fact that a join duplicates records in table 1 that have multiple matches in table 2. So, a join still can create duplicates when you don’t want it.
load *, subfield(applymap('map', Key, '#NV'), '|', 1) as F1, ...
whereby I have never checked if this kind of concatenated mapping is faster as several single ones but I assume it especially if the mapping-loads aren't quite small else are a bit bigger and takes some reload-times.
I will use Applymap then - the fact that the nr. of records never changes alone does make it seem the better option.
Concatenating the fields I want and splitting them again afterwards seems like a good idea. It's not really necessary in my case since I need a LOAD before creating the mapping_table anyway, so the mapping_load is just resident and quite fast even if done three times over, but in other scenarios, that may well be worth trying.
I willintegrate that into a "How-to-document" I have so I remember those tricks and tweaks - like a "missing manual" of my own.