Multiple MapSubstring in a single function call

[pedrohenriqueperna]

Hi,

I have a mapping table with many values that I replace for nothing. A string can contain multiple values that should be replaced and what I'm doing works if I nest/repeat the function.

This works: MapSubstring('ReplaceMap', MapSubstring('ReplaceMap', myField))

I nested it because I thought the function would replace only 1 value within the string, but it happens that if I use a single function < MapSubtring('ReplaceMap', myField) > it replaces multiple values, but not all of them

For instance:

MapTable:

Mapping LOAD * Inline [

" AVENIDA ", " "

" RUA ", " "

" TRAVESSA ", " "

" LOTE ", " "

" ENGENHEIRO ", " "

" QUADRA ", " "

" DE ", " "

];

If I apply the map on a string  ' '&"AVENIDA RUA ENGENHEIRO TRAVESSA DE QUADRA LOTE"&' ' it should give me a blank, but the output value turns out to be "RUA TRAVESSA QUADRA".

Why is this happening? Shouldn't it replace every substring? What's the criteria? I tried changing the order of the valeus in the table but I get the same result every time. If I use a nested mapsubstring it works perfectly, but at this point I'm trying to reduce processing the most I can

[marcus_sommer]

I'm not sure if there isn't an overlapping between the various replacements and that maybe not all could be fulfilled within a single approach - independently of the space-topic. I just played a bit with/without verbatim and also with leading and/or ending spaces ... like stated I couldn't absolutely comprehend why what happens and would need more time for it.

I suggest to make at least some attempts by skipping the space-matter - means replacing the spaces with another unique char, maybe just '|' in beforehand. Further to consider if it's possible to replace leading/ending/inbetween spaces differently.

[pedrohenriqueperna]

I really appreciate your effort.

Indeed the spaces are not really necessary, it's just part of the string that I have to deal with. I'll try to replace every space with "|" and I'll let you know if it worked. If you find out something else I'll be curious to know.

Thanks!

[pedrohenriqueperna]

I changed the logic to replace spaces with "|" and unfortunately got the same result, but it gave me an insight.

It seems to me that the removal of the words happens before the replacement is insert. For instance:

MapTable:

Mapping LOAD * Inline [

"|AVENIDA|", "|"

"|RUA|", "|"

"|TRAVESSA|", "|"

"|LOTE|", "|"

"|ENGENHEIRO|", "|"

"|QUADRA|", "|"

"|DE|", "|"

];

This will give me the same result, although the output seems to do the right transformation:

If I change to "RUA|","|" then it removes correctly the string:

It seems to me that regardless of adding a "|" as replacement, Qlik still sees the value as "RUA|". Maybe it temporary saves in memory with no replacement for then add the replacements after the calculation?

[pedrohenriqueperna]

Thank goodness I made it.

If I add a double "||" and replace "|RUA|","" it works.

It will replace the whole chain (|AVENIDA|) but will keep a "|" to match the next substring. I'll investigate further if it worked without exception, but it seems to be working fine!!