topic Apply map in QlikView

Apply map

NavinReddy — Fri, 31 Jan 2014 05:31:03 GMT

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

Re: Apply map

Anonymous — Fri, 31 Jan 2014 05:49:21 GMT

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

Re: Apply map

NavinReddy — Fri, 31 Jan 2014 06:02:46 GMT

Hi Srikumar,

Nice Explanation ,Thank you so much

Regards,

Niranjan

Re: Apply map

Fri, 31 Jan 2014 06:08:21 GMT

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

Re: Apply map

Fri, 31 Jan 2014 06:09:15 GMT

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

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

Re: Apply map

Fri, 31 Jan 2014 06:21:55 GMT

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

Re: Apply map

er_mohit — Fri, 31 Jan 2014 06:24:22 GMT

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

Re: Apply map

Anonymous — Fri, 31 Jan 2014 06:28:36 GMT

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

Re: Apply map

MK_QSL — Fri, 31 Jan 2014 07:11:50 GMT

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...

Re: Apply map

hic — Sat, 01 Feb 2014 17:57:13 GMT

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

HIC