Multiple MapSubstring in a single function call

[therealdees]

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]

For me it worked like expected:

MapTable:
Mapping LOAD * Inline [
Lookup, Return
" AVENIDA ", " "
" RUA ", " "
" TRAVESSA ", " "
" LOTE ", " "
" ENGENHEIRO ", " "
" QUADRA ", " "
" DE ", " "
];

load *, mapsubstring('MapTable', F1) as F2;
load ''& 'AVENIDA RUA ENGENHEIRO TRAVESSA DE QUADRA LOTE ' & '' as F1 autogenerate 1;

[therealdees]

@marcus_sommer, thanks for the reply! I was just calling out for you help in the other thread I opened, hehe

Everything you suggested got me to progress, but for some reason Mapsubstring is not adding the space as replacement.

"Or" just replied to this thread (deleted the reply), but he's right. The problem is the absence of a space in front of the following substrings, and that is because Mapsubstring is not adding the space set as replacement.

I set the Verbatim = 1 as you suggested. Not really sure why this is happening. Shouldn't it respect the space?

If add ". " as replacement instead, it only adds ".", for e.g

[therealdees]

Did you set the Verbatim = 1 in the testing you made and worked as expected?

[Or]

I deleted my post because I figured Marcus's reply would be more useful than mine since I wasn't able to actually test the code... but if that helped, great.

I think the issue might be that the actions are all being done on the original string, and the modified string with the replacements only comes about after all the substrings are replaced. But no idea if that's actually the case, just guessing.

[therealdees]

@marcus_sommer 

I just removed the Verbatim statament and it worked as expected, lol

Why??? I don't understand. If Qlik's default approach is to remove extra spaces, it should work only with Verbatim = 1, shouldnt it?

[therealdees]

It helped indeed. Thanks!

I'm not sure if it's about changing the original string, actually I don't understand how that would impact the result. It's something related to Qlik's behaviour dealing with spaces. At first, marcus suggestion to use Verbatim = 1 got me through some parts, but since then I changed a lot of stuff to optmize the processing. Some things were redundant, etc.

It seems to work perfectly now, but I'm very curious to understand the reason.

[therealdees]

Actually it's not working. I got back to where I first needed to set verbatim = 1

It's replacing part of strings, e.g " DE " will replace any substring that has "DE" within:

AVENIDA CASA GRANDE will become > CASA GRAN

[therealdees]

It's even more weird, because the spaces within the "from" value are considered, but the space as "to" value is not respected:

" RUA " will only look for strings that holds both spaces. After removing " AVENIDA ", it replaces the substring with nothing, so the result will be "RUA ENGENHEIRO TRAVESSA DE QUADRA LOTE". Given the fact that there's no "RUA " in the mapping table, but " RUA ", it doesn't replace the substring. So the problem seems to be only with the space as replacement, not within the value

[therealdees]

I'm just trying everything that comes to my mind, removing, adding spaces, etc, and I noticed it actually adds the space, although the table does not display extra spaces by default.

If I change " AVENIDA "," " to a double space > " AVENIDA ","  ", it does add 2 spaces as shown in the print below. But for some reason it doesn't replace the string. Maybe the ASCII chars are different? It's not like it's removing the spaces as I suspected. It adds, but the word with spaces seems to no be recognized, only those that already have leading and trailing spaces and the first substring that receives an extra space with ' '&Mapsubstring([...])&' '