topic Re: Mapping load and apply map in QlikView

Mapping load and apply map

Fri, 27 Dec 2013 08:29:09 GMT

hi,

can any body tell me how these are implimented and how applymap is best copared with join how it becomes faster performance

compred to join in qlikview .

regards

Mahesh

Re: Mapping load and apply map

tresB — Fri, 27 Dec 2013 08:36:34 GMT

Go through the attached doc for clear explanation.

If you can solve the problem using an ApplyMap/ lookup function instead of a join, I strongly recommend that

you do so.

Bottom line is that you’ll have to weigh pros and cons. Don’t join if you can avoid it. If performance

is important and you experience a noticeable improvement when you join, then you probably

should join. But ask yourself what the implications are. Is the script still manageable? Can a user

understand how the formula should be written? Would it be better to put this transformation in the

data warehouse or in the metadata layer?

The best join is often the one that never is made. Often – but not always.

Re: Mapping load and apply map

maxgro — Fri, 27 Dec 2013 08:38:31 GMT

Hi Malesh,

see this good doc for ApplyMap

http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

Regards

Re: Mapping load and apply map

salto — Fri, 27 Dec 2013 08:50:44 GMT

Hello,

an example in which I use mapping instead of joining is when I have a Code field (for example, Country Code) and instead of joining it to the transactions table to retrieve the Country Name, I map the Code to the Country Name.

I first create a mapping table (that, by the way, will not remain in the data model with no need to drop it):

1.- Mapping:

Countries:

mapping load

Code,

Name as CountryName;

SQL Select *

FROM MYDB.dbo."CountryTable";

Then, when I load the customers, I map the Country Code field:

Customers:

LOAD

"No_" as CustomerID,

Name as CustomerName,

applyMap ('Countries', "Country Code",'No country assigned') as CountryName;

SQL Select *

FROM MYDB.dbo."CustomerTable";

2 .- Instead of mapping, you have the option to left join the customer table with the Countries:

Countries:

load

Code as CountryCode,

Name as CountryName;

SQL Select *

FROM MYDB.dbo."CountryTable";

TempCustomers:

LOAD

"No_" as CustomerID,

Name as CustomerName,

"Country Code" as CountryCode;

SQL Select *

FROM MYDB.dbo."CustomerTable";

Customers:

left join (TempCustomers) load * resident Countries;

drop table Countries;

drop table TempCustomers;

This option forces you to load the customer table twice, so in this case I prefer to do a mapping. As HIC says: The only time that you cannot use Applymap() instead of a join is if you want to fetch more than one single corresponding value (record) from the second table.

Hope this helps.

Re: Mapping load and apply map

Gysbert_Wassenaar — Fri, 27 Dec 2013 08:58:30 GMT

See this blog post: Don't join - use Applymap instead

And maybe this one: To Join or not to Join

And this document: Joins and Lookups

Re: Mapping load and apply map

Fri, 27 Dec 2013 10:08:49 GMT

Thank u so much i got clarified the doubt.

regards

Mahesh thalluri

Re: Mapping load and apply map

salto — Fri, 27 Dec 2013 10:18:36 GMT

Glad to know it solved your problem.

Regards!

Re: Mapping load and apply map

Fri, 27 Dec 2013 12:40:06 GMT

it is fine let me know if there is any problem in future while using incremental load for updated records if i use

these lookupfunctions.

regards

Mahesh t

Re: Mapping load and apply map

ngulliver — Fri, 03 Jan 2014 08:45:14 GMT

Hi, Mahesh.

Take a look at this blog on the best uses for ApplyMap.

http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

Regards,

Neil

Re: Mapping load and apply map

Fri, 03 Jan 2014 13:38:02 GMT

Hi Mahesh,

Generally communities are state lookup function give performance disgrace