topic Re: Functions in QlikView

Functions

Wed, 10 Dec 2014 12:21:12 GMT

Can anyone provide some material with example for function "Map substring()"

Thanks in Advance

Jana.

Re: Functions

anbu1984 — Wed, 10 Dec 2014 12:28:37 GMT

Example from QV help

map1:

mapping load * inline [

x, y

1, <one>

aa, XYZ

x, b ] ;

MapSubstring ('map1', 'A123') returns 'A<one>23'

MapSubstring ('map1', 'baaar') returns 'bXYZar'

MapSubstring ('map1', 'xaa1') returns 'bXYZ<one>'

Re: Functions

Wed, 10 Dec 2014 12:35:55 GMT

Hi Jana

The mapsubstring() function is similar to the applymap() function, except that it maps part of a string instead of the full string.

In order to use these functions, and any mapping functions, you need to declare a mapping table. This is your "lookup" table and just comprises two fields- the reference field first, and the description field second.

You can then reference the mapping table in functions to replace codes in the data with text. The applymap() function replaces an entire field value with the description, and the mapsubstring() function replaces part of it.

See below code to experiment for yourself (this will load on it's own in a QV script)

//Load the mapping info - in this case Country codes to country names

Country_Map:

Mapping load *;

LOAD * INLINE [

Country_Code, Country_Name

UK, United Kingdom

BR, Brazil

RU, Russia

IN, India

CH, China

];

//Load the data that needs to be mapped

Messy_Data:

LOAD * INLINE [

Row, Data 1, Data 2

1, BR, In sunny BR

2, BR, I love Rio in BR!

3, RU, RU is very cold in winter

4, UK, The UK is very rainy

5, CH, CH

];

Left join (Messy_Data)

Load Row, //This will automatically join on the field Row

ApplyMap('Country_Map',[Data 1]) as Fn_ApplyMap,

MapSubString('Country_Map',[Data 2]) as Fn_MapSubString

resident Messy_Data; //joins it onto itself

The result is this table:

Re: Functions

Wed, 10 Dec 2014 12:39:48 GMT

Row	Data 1	Data 2	Fn_ApplyMap	Fn_MapSubString
1	BR	In sunny BR	Brazil	In sunny Brazil
2	BR	I love Rio in BR!	Brazil	I love Rio in Brazil!
3	RU	RU is very cold in winter	Russia	Russia is very cold in winter
4	UK	The UK is very rainy	United Kingdom	The United Kingdom is very rainy
5	CH	CH	China	China

As you can see the ApplyMap() function has replaced the entire field value, where as the

Other points to note:

- The mapping functions are case sensitive

- MapSubstring() will replace the lookup values multiple times in a string, no matter where they are so take care! in the example above, the word "BRITISH" will have the BR replaced to "Brazil" and would look like "BrazilITISH"

- Using mapping functions is far more efficient thans using lookup functions - there is plenty of material else where that explains why.

Erica

Re: Functions

its_anandrjs — Wed, 10 Dec 2014 12:46:04 GMT

Hi,

From Qv Help

The MapSubstring function is used to map parts of any expression to a previously loaded mapping table. The mapping is case sensitive and non-iterative and substrings are mapped from left to right. The syntax is:

mapsubstring('mapname', expr)

This function can be used with mapping parts of any expression on a previously loaded mapping table. The mapping is case sensitive and non-recursive. The substrings are mapped from the left to the right. Mapname is the name of a mapping table previously read by a mapping load a mapping select statement. The name must be enclosed by single straight quotation marks. Expr is the expression whose result should be mapped by substrings.

Examples:

// Assume the following mapping table:

map1:

mapping load * inline [

x, y

1, <one>

aa, XYZ

x, b ] ;

MapSubstring ('map1', 'A123') returns 'A<one>23'

MapSubstring ('map1', 'baaar') returns 'bXYZar'

MapSubstring ('map1', 'xaa1') returns 'bXYZ<one>'

Regards

Anand