Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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!
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?
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!!