topic Re: Difference between left join vs apply map in QlikView

Difference between left join vs apply map

Tue, 15 Jul 2014 06:28:28 GMT

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

i know its good for optimization

but how ??

Re: Difference between left join vs apply map

ngulliver — Tue, 15 Jul 2014 08:33:50 GMT

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

Re: Difference between left join vs apply map

hic — Tue, 15 Jul 2014 10:34:59 GMT

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

HIC

Re: Difference between left join vs apply map

datanibbler — Mon, 19 Jun 2017 06:51:46 GMT

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

Re: Difference between left join vs apply map

vardhancse — Mon, 19 Jun 2017 06:54:54 GMT

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

Re: Difference between left join vs apply map

datanibbler — Mon, 19 Jun 2017 07:06:41 GMT

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.

Re: Difference between left join vs apply map

hic — Mon, 19 Jun 2017 07:06:52 GMT

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.

HIC

Re: Difference between left join vs apply map

marcus_sommer — Mon, 19 Jun 2017 07:29:47 GMT

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

Re: Difference between left join vs apply map

datanibbler — Mon, 19 Jun 2017 07:54:44 GMT

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

Re: Difference between left join vs apply map

ysj — Wed, 17 Jan 2018 14:08:36 GMT

Re: Difference between left join vs apply map

himanshi — Wed, 28 Aug 2019 08:57:03 GMT

Hi,

You don't have to create three mapping tables to get the fields through applymap.You can do it thought one mapping table like the below example.

Map Countries:

Mapping Load

Country Code,

Country name&'|'&Country Currency as Country data

Resident Country;

Customers_Tab:

Load *,

Subfield(Applymap('Map Countries',Country Code,null()),'|',1) As Country Name,

Subfield(Applymap('Map Countries',Country Code,null()),'|',2) As Country Currency

Resident Customers;