topic Re: Multiple MapSubstring in a single function call in Connectivity & Data Prep

Multiple MapSubstring in a single function call

pedrohenriqueperna — Wed, 10 Jan 2024 19:44:52 GMT

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

Re: Multiple MapSubstring in a single function call

marcus_sommer — Thu, 11 Jan 2024 14:10:24 GMT

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;

Re: Multiple MapSubstring in a single function call

pedrohenriqueperna — Thu, 11 Jan 2024 14:20:43 GMT

@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

Re: Multiple MapSubstring in a single function call

pedrohenriqueperna — Thu, 11 Jan 2024 14:22:45 GMT

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

Re: Multiple MapSubstring in a single function call

Or — Thu, 11 Jan 2024 14:27:11 GMT

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.

Re: Multiple MapSubstring in a single function call

pedrohenriqueperna — Thu, 11 Jan 2024 14:28:35 GMT

@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?

Re: Multiple MapSubstring in a single function call

pedrohenriqueperna — Thu, 11 Jan 2024 14:32:54 GMT

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.

Re: Multiple MapSubstring in a single function call

pedrohenriqueperna — Thu, 11 Jan 2024 15:04:35 GMT

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

Re: Multiple MapSubstring in a single function call

pedrohenriqueperna — Thu, 11 Jan 2024 15:00:36 GMT

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

Re: Multiple MapSubstring in a single function call

pedrohenriqueperna — Thu, 11 Jan 2024 15:48:26 GMT

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([...])&' '

Re: Multiple MapSubstring in a single function call

marcus_sommer — Thu, 11 Jan 2024 16:14:20 GMT

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.

Re: Multiple MapSubstring in a single function call

pedrohenriqueperna — Thu, 11 Jan 2024 16:21:37 GMT

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!

Re: Multiple MapSubstring in a single function call

pedrohenriqueperna — Thu, 11 Jan 2024 16:54:54 GMT

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?

Re: Multiple MapSubstring in a single function call

pedrohenriqueperna — Thu, 11 Jan 2024 17:02:46 GMT

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