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