Mapping Load / Apply Map versus Left Join

[pkelly]

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

[pkelly]

Anyone got any feedback on this one?

[johnw]

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.

[#4]

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.

[#5]

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

[johnw]

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:

[#7]

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!

[#8]

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.

[#9]

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.

[#10]

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