Difference between left join vs apply map

[#1]

for my knowledge i want to know difference between left join and apply map

i know its good for optimization

but how ??

[ngulliver]

Hi, Navneet.

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.

Regards,

Neil

[hic]

A Join may result in more records than any of the original tables, whereas an ApplyMap never changes the number of records.

HIC

[datanibbler]

Hi Henric,

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?

Thanks a lot!

Best regards,

DataNibbler

[vardhancse]

Join:

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.

Applymap/Lookup:

The ApplyMap script function is used for mapping the output of an expression to a previously loaded mapping table

[datanibbler]

Thanks Sasi!

That looks a lot like the help_file 😉

I know what a LEFT JOIN should do - but it does not always work the way you think it should.

I usually prefer Applymap, even if I need to use several mapping_tables.

[hic]

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.

See also Don't join - use Applymap instead

HIC

[marcus_sommer]

In addition to Henric answer you could concat several mappings within a single one like:

map:

mapping load Key, F1 & '|' & F2 & '|' & F3 resident AnySource;

and split them with subfield() again:

table:

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.

- Marcus

[datanibbler]

Hi Henric and Marcus,

thanks a lot for your answers!

I will use Applymap then - the fact that the nr. of records never changes alone does make it seem the better option.

@ Marcus

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 will integrate that into a "How-to-document" I have so I remember those tricks and tweaks - like a "missing manual" of my own.

Best regards,

DataNibbler

[ysj]