Apply map

[NavinReddy]

Hi All

In which scenario we are using apply map ,instead of left join

Any Specific reason please guide me i am learning Apply map function

Thanks & Regards

Niranjan

[Anonymous]

Hi Niru,

The below example will helpful for you,

First scenario:

Table 1:

Key,Place

1, Ind

2, Aus

3, NZ

Table2:

Key ,City

1,  a

2,  b

3,  c

the above Senerio  we can use apply map

Second scenario:

Table 1:

Key,Place

1, Ind

2, Aus

3, NZ

Table2:

Key ,City

1,  a

2,  b

3,  c

3,  d

if Place need to add city in Table1 Use theLeftJoin to the Key,

because of  Key 3 have two city.,

If we using apply map for Second scenario the result will be(one city missing)

Key,Place,city

1, Ind,a

2, Aus,b

3, NZ,c

if we use the left join Second scenario   the result

Key,Place,city

1, Ind,a

2, Aus,b

3, NZ,c

3, NZ,d

[NavinReddy]

Hi Srikumar,

Nice Explanation ,Thank you so much

Regards,

Niranjan

[#4]

Hi Niranjan

The ApplyMap function is used for mapping any expression to a previously loaded mapping table

for example

Assume that you have one master table and one temp table

in master table you have Branch Code and Branch name is available....... and in Temp table Branchcode and Merged Branch code is available. With the help of that branch code you want to map and get the branch name. find the below example script you will get an idea.

EG:

Master:

Mapping LOAD * Inline [

Code, CompanyName

101, Ahmedabad

102, Bangalore

103, Chennai

104, Mumbai

105, Hyderabad

106, Delhi

];

Temp:

LOAD * Inline [

BranchCode, MergeCode

101000EQ, 101

102000EQ, 101

103000EQ, 101

104000EQ, 104

105000EQ, 105

106000EQ, 106

];

Temp1:

LOAD *,

left(BranchCode,3) as code,

if(left(BranchCode,3)<>MergeCode,MergeCode,left(BranchCode,3)) as NewBranch

Resident A;

DROP Table A;

Temp2:

LOAD *,

ApplyMap('Master',NewBranch,'Not Mapped') as BranchCodeNew

Resident B;

DROP Table B;

Load the above script in your application and reload..Then in front end take the table box and add the all fields. Finally you will get some Idea. Hope this will clear you

[#5]

Hi Niranjan

The ApplyMap function is used for mapping any expression to a previously loaded mapping table

for example

Assume that you have one master table and one temp table

in master table you have Branch Code and Branch name is available....... and in Temp table Branchcode and Merged Branch code is available. With the help of that branch code you want to map and get the branch name. find the below example script you will get an idea.

EG:

Master:

Mapping LOAD * Inline [

Code, CompanyName

101, Ahmedabad

102, Bangalore

103, Chennai

104, Mumbai

105, Hyderabad

106, Delhi

];

Temp:

LOAD * Inline [

BranchCode, MergeCode

101000EQ, 101

102000EQ, 101

103000EQ, 101

104000EQ, 104

105000EQ, 105

106000EQ, 106

];

Temp1:

LOAD *,

left(BranchCode,3) as code,

if(left(BranchCode,3)<>MergeCode,MergeCode,left(BranchCode,3)) as NewBranch

Resident A;

DROP Table A;

Temp2:

LOAD *,

ApplyMap('Master',NewBranch,'Not Mapped') as BranchCodeNew

Resident B;

DROP Table B;

Load the above script in your application and reload..Then in front end take the table box and add the all fields. Finally you will get some Idea. Hope this will clear you

[#6]

hi

suppose you have two table Table1 and Table2.

Suppose in Table1 field are emp code and emp name.

Suppose in Table2 field are emp code and emp Sal

now if you want field emp name in Table2.

then use ApplyMap in Table2

[er_mohit]

doing left join on the bases of table you get only that data which are common in both table but

if you want to specify the other data values which are not being in your table then it doesn't come through left join it for that purpose we use applymap and the best thing is your table has been dropped when you mapping write mapping load

and thus it also reduce the memory

Regards

Mohit

[Anonymous]

for this i will say apply map has certain advantage it is faster , safer way of bringinging missing column

in tables

its like let you need unit price in four , five tables and ofourse you can load and join()  in it by left join

to required no of tables  but you see evry time you load and use join which are in qlikview atleast must

be used least no of times as they are prone to data corruption if used in wrong way.

on the other hand for apply map you need to load unit price only once and use apply map it required tables

and therefore you need to load only once and it saves times.

anant

[MK_QSL · Accepted solution]

We should use ApplyMap instead of Join

ApplyMap is giving better performance

ApplyMap table should be loaded first before it's use.

At the end of the script, this table will disappear !

But we need to make sure that ApplyMap table should have only two column/dimensions.... First should be the lookup dimension and second should be the value we need to get

There should not be any duplication in ApplyMap otherwise it will create duplicated rows..

Hope this will help....

Example:

ProductTable:

Mapping Load * Inline

[

     Product, Name

     P1, Product 1

     P2, Product 2

     P3, Product 3

];

Sale:

Load *, ApplyMap('ProductTable',ProductID, 'Unknown') as PartNameINLINE

[

     Customer, ProductID

     C1, P1

     C2, P2

     C4, P4

];

Here ApplyMap will refer to the ProductTable ... It will match ProductID of Sale table with Product of ProductTable and give PartName (2nd Dimension of ProductTable). Wherever there is no Product compare to ProductID, it will give ProductName as Unknown (Third Parameter)....

Hope this will help...

[hic]

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

HIC