topic Re: Mapping Load / Apply Map versus Left Join in QlikView

Mapping Load / Apply Map versus Left Join

pkelly — Wed, 18 Aug 2010 16:22:01 GMT

Mapping Load / Apply Map was functionality which was shown to us during our initial training session with QlikTech many moons ago but it is not something that I have used within my scripts instead using left joins to get say description of a code into my QVD table.

Not really sure what the benefits or differences are between using mapping and left joins and would be grateful if someone could elaborate on this for me...

Many Thanks

Mapping Load / Apply Map versus Left Join

pkelly — Fri, 20 Aug 2010 22:06:08 GMT

Anyone got any feedback on this one?

Mapping Load / Apply Map versus Left Join

johnw — Fri, 20 Aug 2010 23:47:23 GMT

I suspect the main difference (other than aesthetic) would be performance, but I haven't done any performance testing to back that up. I would guess that mapping loads are a typically a little faster, but that's pure speculation.

One advantage of mapping loads is that you can use the mapped value immediately, rather than waiting for a second left join. For instance, let's say you're loading a unit price from one table, and a quantity from another table, and want your resulting table to have unit price, quantity AND total price. With a mapping load, you can set up the price table as a map with one load, an in a second load, applymap once to get the unit price, and applymap again when calculating the total price. With left joins, you first need to load and join both tables, then do a final left join to calculate the total price. So you save a load by using a mapping table here, which probably saves time.

You can also apply a whole lot of maps all in the same load, rather than one by one left joining each new table onto the main table. Again, I strongly suspect the mapping load approach would be faster when you have more than one like this. But again, I haven't performance tested it, so I can't say for sure.

Mapping Load / Apply Map versus Left Join

Fri, 20 Aug 2010 23:59:06 GMT

Hi,

i assume that if you have to add multiple fields from an other table than i would prefer JOIN.

If i want to set a default Value if a field is missing i would use APPLYMAP. Also if i would calculate a new field or to use as part of a WHERE (also in COMBINATION with EXISTS) clause, APPLYMAP would be my first choice.

Mapping Load / Apply Map versus Left Join

Sat, 21 Aug 2010 14:47:18 GMT

I strongly prefer using apply map. When you have a long mapping list and for some reason there's duplicate entries and you do a left join you will multiply the number of records. Apply map is therefore the 'safer' solution.

Here's an interesting read on peformance of apply map vs. using simple if statements.

http://guerrillabi.com/Mapping_Load_vs._Apply_Map_vs._If

Mapping Load / Apply Map versus Left Join

johnw — Mon, 23 Aug 2010 21:29:01 GMT


Lukas Ickerott wrote: Here's an interesting read on peformance of apply map vs. using simple if statements.
http://guerrillabi.com/Mapping_Load_vs._Apply_Map_vs._If

From the linked analysis, "The table join method is discarded on forehand because it will run though every row in the loaded table."

This appears to have been a mistake. We are loading from QVDs, and therefore there is a significant performance benefit to keeping the load optimized. A left join keeps the load optimized. All tested mapping methods BREAK the optimization. This matters. It turns out that a left join is the fastest option for a small number of mapping tables, at least for my sample data (5,000,000 rows, ID field, 10 other fields with values from 0 to 3).

An additional group of options that wasn't tested was doing an optimized load, THEN doing a left join with mapping. It turns out that none of these approaches were competitive, but they seemed worth testing.

For a larger number of mapping tables, the fastest approach appears to be breaking the optimization and using a MAP USING.

Also, the if() does seem very fast, as Seebach noted in his analysis. I would not have expected that. And applymap() is, to me anyway, shockingly slow. I will probably use it less often in the future.

Here's the raw data:

And assuming the trend is linear, as I suspect:

Mapping Load / Apply Map versus Left Join

Mon, 09 May 2011 20:27:18 GMT

interesting topic lost....can any developer of the tool or anyone who knows the working algo of the applymap explain the flow of its working and thereof where and when it to be appropriately used. Thanks!

Re: Mapping Load / Apply Map versus Left Join

Wed, 08 Jun 2011 20:25:16 GMT

There is an advantage from the user's perspective: applymap allows for specifying a value for unmatched values. Left/outer joins will not let you drillthrough by the null values (you can specify something other than '-' but QV still does not allow for filtering on this value).

Furthermore, I typically use both an outer join and the applymap: I outer join the tables so I can get more than one field from the outer joined table; I use the applymap on one field from the outer joined table so I can use it as a list box for filtering and allow filtering for the unjoined rows.

An example: we have patients enrollmented in programs; we make program specific calls to the patients. So, we have an enrollment table and call log table. The call log table has a field call type (values are "Success", "Unsuccessful"). Left outer join enrollments and call log. If a list box is created on the call type field, there will be three values: Success, Unsuccessful, -. "-" would be for all patients who have never had a call; I cannot filter by this to determine who these patients are. If I create separate applymap, I can specificy "Not Called" as the unjoined mapping value, so my list box would have: success, unsuccessful, and not called as values. I can now filter by not called to determine the patients who have not been called.

Hope this makes sense... kind of peripatetic answer.

Mapping Load / Apply Map versus Left Join

Thu, 09 Jun 2011 10:34:30 GMT

Though , i am not proficient in Qlikview as the reply we receive from above users.

But difference ipersonnel felt is listed below :

1 . Mapping table is table which is only available during reloading of data. Itsapplicable to tables where only 2 fields are present i:e tablesof this format can only be mapped.

It's Temporarytable which get terminated automatically as the script execution get completed.

You can use themapping field in Nnumber of tables with the Apply Map function.

This takes less time in comparison to Join as tablefields is coming directly while reloading.

2. In Join you have to fetch data from tables which arepresent on Disk or any other storage. Thistakes Little more time in comparisonto Mapping tables.

Advantage is when youhave more than 2 filed which has tojoined in tables . Apply map can not be used in this case.

Mapping Load / Apply Map versus Left Join

Fri, 10 Jun 2011 18:13:22 GMT

In applymap u will not get the common fieldname but in the left join u will get the common field name.

for example:

1 table consist two columns : 1.Country

2.Capital.

2 table consist two columns: 1.Capital

2.New Capital.

now writing left join between these two tables will give you three columns:Country,Capital,New Capital

but using apply map will give you two columns:Country,NewCapital

i hope u will get clear by this...

puneet

Re: Mapping Load / Apply Map versus Left Join

Tue, 10 Jun 2014 19:05:19 GMT

You are wrong puneet. We can still get the common filed column in the new consolidated table.

Re: Mapping Load / Apply Map versus Left Join

patricio — Tue, 10 Jun 2014 19:38:04 GMT

I want to share an additional benefit that I found in the ApplyMap if compared with the left join when modifying a script.

Imagine that you have the Color Code in your table and one day you decide to add the description that is in the color table. If you use a left join you risk to add additional records if for any reason there are duplicates in your Color table.

By using the applymap you limit your testing after the change because you are 100% sure that you are not adding additional records even if there are duplicates in your color table.

I agree with you that you should not have duplicates in your Color table but if instead of the Color is any other Excel table maintained by users without validation the applymap will become less risky.

Re: Mapping Load / Apply Map versus Left Join

AnandKu — Tue, 14 Feb 2023 09:37:20 GMT

the link is dead, here is the saved copy

Re: Mapping Load / Apply Map versus Left Join

marcus_sommer — Tue, 14 Feb 2023 14:39:12 GMT

Here an update to the matter:

let start = now();
t: load recno() as ID, mod(recno(), 4) as Status, chr(ceil(rand() * 127)) & chr(ceil(rand() * 127)) as X
autogenerate 75000000;
let noRows = noofrows('t'); let end = now(); let time = time('$(end)' - '$(start)'); trace '$(time)';
c: load $(noRows) as Records, -1 as No, 'raw-data load' as Type, '$(end)' as End, '$(start)' as Start, '$(time)' as Time autogenerate 1;

let start = now();
store t into Dummy.qvd (qvd); drop tables t;
let end = now(); let time = time('$(end)' - '$(start)'); trace '$(time)';
c: load 0 as Records, 0 as No, 'raw-data store' as Type, '$(end)' as End, '$(start)' as Start, '$(time)' as Time autogenerate 1;

m: mapping load recno() - 1 as Status, chr(recno()+64) as Return autogenerate 4;
l: load recno() - 1 as Status, chr(recno()+64) as Return autogenerate 4;

let start = now();
t: load * from Dummy.qvd (qvd);
let noRows = noofrows('t'); let end = now(); let time = time('$(end)' - '$(start)'); drop tables t; trace '$(time)';
c: load $(noRows) as Records, 1 as No, 'no processing' as Type, '$(end)' as End, '$(start)' as Start, '$(time)' as Time autogenerate 1;

let start = now();
t: load *, applymap('m', Status) as StatusX from Dummy.qvd (qvd);
let noRows = noofrows('t'); let end = now(); let time = time('$(end)' - '$(start)'); drop tables t; trace '$(time)';
c: load $(noRows) as Records, 2 as No, 'applymap' as Type, '$(end)' as End, '$(start)' as Start, '$(time)' as Time autogenerate 1;

map Status using m;
let start = now();
t: load * from Dummy.qvd (qvd);
let noRows = noofrows('t'); let end = now(); let time = time('$(end)' - '$(start)'); drop tables t; trace '$(time)';
c: load $(noRows) as Records, 3 as No, 'map using' as Type, '$(end)' as End, '$(start)' as Start, '$(time)' as Time autogenerate 1;
unmap *;

let start = now();
t: load *, pick(Status - 1, 'A', 'B', 'C', 'D') as StatusX from Dummy.qvd (qvd);
let noRows = noofrows('t'); let end = now(); let time = time('$(end)' - '$(start)'); drop tables t; trace '$(time)';
c: load $(noRows) as Records, 4 as No, 'pick' as Type, '$(end)' as End, '$(start)' as Start, '$(time)' as Time autogenerate 1;

let start = now();
t: load * from Dummy.qvd (qvd); left join(t) load * resident l;
let noRows = noofrows('t'); let end = now(); let time = time('$(end)' - '$(start)'); drop tables t; trace '$(time)';
c: load $(noRows) as Records, 5 as No, 'join' as Type, '$(end)' as End, '$(start)' as Start, '$(time)' as Time autogenerate 1;

drop table l;

which results in:

whereby the if-loop from the origin example was replaced by a pick() approach and no artificial where-clause is applied for the map using load because it's not needed anymore (maybe it was a bug within the old releases or Qlik changed the processing). To mention is further that the results has some differences between multiple runs of about 10 - 15 seconds to each one of the approaches which means that my system/network-storage has some external impacts. Without measuring each run I think the showed results is quite the average.

I must admit that I had expected bigger differences between the methods especially for more run-time benefits of the applymap(). Beside this the test is very simplified and with more complex data and requirements the run-time comparing may be quite different.

In my real life I use meanwhile nearly always mappings whereby not seldom multiple ones horizontally and vertically nested (both in lookups as well as by the returns) and they work very well and fast - better as all the other approaches before.

Re: Mapping Load / Apply Map versus Left Join

crgrossman — Tue, 14 Feb 2023 15:07:16 GMT

I would use the apply map for business values that are not standardized that have to managed manually or through spreadsheets, and left join if the business logic is coming from a static source like a database or API.

The apply map also allows you to handle exceptions in a single line of code that's easy to understand.

Re: Mapping Load / Apply Map versus Left Join

Ally — Thu, 28 Mar 2024 10:14:16 GMT

Going through the thread, one difference that hasn't been highlighted is that if you have duplicate values in your second table (let's call it table 'B') and have values assigned to those duplicates,

when using left join you will see all those values appended into your original table 'A'.

If you use a mapping load the duplicate values will not show up in table 'A' and only one row will be appended from table 'B' - it will pick the first one it sees and you'll only see that one value in the 'A' table.

Re: Mapping Load / Apply Map versus Left Join

koustubh — Thu, 17 Oct 2024 04:48:55 GMT

I doubt that observation,
using applyMap function also we can get 3 columns - Country,Capital,New Capital