topic Re: Use Match functionality to look up values in QlikView

Use Match functionality to look up values

Thu, 23 Apr 2015 09:21:08 GMT

Hello everybody,

Suppose I load 2 tables into Qlikview and want to add a column to the first table that signifies whether a value is found in a certain column of table 2.

In Excel I would use 2 sheets and use the match function to identify if a value is found in a column in the second table and assign zero if #N/A and 1 if match gives a number > 0.

My question is, how would I write something like this into my Qlikview script?

Re: Use Match functionality to look up values

buzzy996 — Thu, 23 Apr 2015 16:08:37 GMT

use ApplyMap() function.

from reference manully,

ApplyMap( 'mapname', expr [ , defaultexpr ] )

Maps any expression on a previously loaded mapping table. Expr is

the expression whose result shall be mapped. Mapname is the name

of a mapping table previously loaded by a mapping load or mapping

select statement (see page 326). The name must be quoted

with single quotes. Defaultexpr is an optional expression, which

will be used as default mapping value if the mapping table does not

contain any matching value for expr. If no default is provided, the

value of expr is returned as is.

Examples:

// Assume the following mapping table

map1:

mapping load * inline [

x,y

1,one

2,two

3,three];

ApplyMap( 'map1' , MyVal ) returns 'two' if MyVal = 2

ApplyMap( 'map1' , MyVal ) returns 4 if MyVal = 4

ApplyMap( 'map1' , MyVal, 'x' )

returns 'x' if MyVal <> 1, 2 or 3

ApplyMap( 'map1' , MyVal, null() )

returns null if MyVal <> 1, 2 or 3

ApplyMap( 'map1' , MyVal, null() )

returns 'one' if MyVal = 1

Re: Use Match functionality to look up values

maxgro — Thu, 23 Apr 2015 19:25:12 GMT

secondtable:

load * inline [

field1, field2

1,a

2,b

3,c

];

firsttable:

load

f1, if(exists(field2,f1), 'exists', 'not exists') as flag_f1_in_field2

inline [

];