- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mapping Load / Apply Map versus Left Join
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Anyone got any feedback on this one?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- « Previous Replies
-
- 1
- 2
- Next Replies »