topic Re: apply map in QlikView

apply map

Anonymous — Fri, 07 Sep 2018 07:10:01 GMT

can someone please help in explaining applymap functionwith example,why we use it,how it works,

Re: apply map

Anonymous — Fri, 07 Sep 2018 07:27:34 GMT

The MAPPING statement provides an alternative to the JOIN statement in a very specific scenario: when you want to replace a single key value with a value from a lookup (mapping) table.

We prefix ' Mapping' to the load statement to tell Qlikview that a table is a mapping table.

The mapping table is called with the 'Applymap()' function

Sample:

MapTable:

Mapping

LOAD * Inline

[

RegionName,RegionCode

Asia,AS

Europe,Eur

MEA,ME

];

Test:

LOAD RegionName,

ApplyMap('MapTable',RegionName,'N/A')

Resident main;

Drop table MapTable;

Thanks,

Re: apply map

Anonymous — Fri, 07 Sep 2018 07:30:01 GMT

whats the result of the above statement?

Re: apply map

Anonymous — Fri, 07 Sep 2018 07:37:29 GMT

Hi,

PFA to observe the result produced by apply Map.

Thanks

Re: apply map

captain89 — Fri, 07 Sep 2018 12:12:05 GMT

Hi,

it's very similar to the V.LOOKUP function in excel.

For example if you have two tables like this in your database:

Inventory:

Code, Quantity

a001, 100

a002, 50

a003, 70

Products:

Code, Description

a001, first code

a002, second code

you can join them using Code but if you create a table like this:

Code, Quantity, Description

a001, 100, first code

a002, 50, second code

a003, 70, -

but you get a null value in the description field... and you can't select it

if you use applymap you can put a value in the description field:

map_description:

mapping load Code, Description resident Products;

Inventory:

load

Code,

Quantity,

Applymap('map_description, Code, 'not found') as Decription.

applymap is far better.. in this case you can drop the Product table and your database is simpler and less CPU expensive...

In this way you can select "not found" values without any problem.

The only problem of applymap comes when your mapping table isn't indexed.

If you have a situation like this:

Product:

Code, Description

a001, first code

a002, second code

a002, third code

if you have duplicate values applymap put the first one. Joins are better in this case because the error is visible early.