The MapSubstring() function is a powerful alternative to using nested Replace() or PurgeChar() functions.
MapSubstring(), unlike it’s siblings ApplyMap() and Map, will apply multiple mappings from the mapping table. Here’s an example.
ReplaceMap:
MAPPING LOAD * INLINE [
char replace
)
)
”
,
/
–
] (delimiter is ‘ ‘)
;
TestData:
LOAD
*,
MapSubString(‘ReplaceMap‘, data) as ReplacedString
;
LOAD * INLINE [
(415)555-1234
(415)543,4321
“510”123-4567
/925/999/4567
] (delimiter is ‘ ‘)
;
In field “ReplacedString“, all the characters matching the first field of the map (“char”) are replaced with a backslash as shown in this table. This makes it ready for parsing with a function like SubField().
Another usage is an alternative to nested PurgeChar() to remove multiple characters. A blank is used as the mapping character. For example:
PurgeMap:
MAPPING LOAD * INLINE [
char replace
)
)
“
,
/
–
] (delimiter is ‘ ‘)
;
MapSubString(‘PurgeMap’, Data) will produce results like this:
-Rob
Hola
Tengo dos tablas, y ambos tienen fechas.
Tabla1
mes1
Ventas1
Tabla2
mes2
Ventas2
y necesito que al apretar mes1 me traiga las Ventas1 y las Ventas2 para poder restarlas.
De antemano gracias.
nested PurgeChar? not required, since the syntax is PurgeChar({item},'{string made up of characters to be purged}’), i.e. PurgeChar(‘Hello There’,’elo’) will return ‘H Thr’ — you don’t have to do multiple single-character purges.
@David,
Good point. Your solution is cleaner than mine. Using the mapping is unnecessary.
Thumbs up 🙂
It was very helpful.
Any thoughts on how to remove tabs from data (e.g. Chr(9) or t)??
Thanks for the post – always helpful!
The easiest way to remove tabs is with
PurgeChar(string, chr(9))
If you want to map them instead, you can include them in your mapping table like this:
MAPPING LOAD
chr(9) as char, ‘x’ as replace
AUTOGENERATE 1
;
You can still use the INLINE table and the addition will be concatenated.