MapSubstring in left X characters

[#1]

Hi, i'm using a MapSubstring and it doesn't work like hope, there are someones cases with problems.

Example:

I need to replace the text 'Al ' to 'Al. ' but only if match the first 3 characters including space ('Al '). So it not must replace the word 'Catalana' to 'Catal.ana' or to 'Catana' like it does, because there is not space, and nor because there are not the 3 first characters of the data in field.

The lenght of the characters to search is different. Example:

Replace the text 'Barrio ' to 'Bo. ' but only if match the first 7 characters.

I included a qlikview file to help find a solution, with original data, wanted results, and bad results .

Thanks

[#2]

Hi,

do you have only the 6 mentioned values as in your example or are we talking about hundreds or thousands of items. If handy enough, why don't you do a full map with the full text string?

Another idea might be to apply the mapping only to the first word maybe? So you could use

capitalize(MapSubstring('map1',lower(Left(tmpTabla.mapAddress, Index(tmpTabla.mapAddress, ' ', 1))))) & Mid(tmpTabla.mapAddress, Index(tmpTabla.mapAddress, ' ')+1) as Address

It seems to work for me. Maybe you test it with your full data...

cheers

Florian

[#3]

it's quite aproximate, but i found some cases where doesn't work.

The number of rows in table are thousands and the posibilities as we receive the address are to become crazy.

For example, some addresses are like 'Alda.Mayor 12, 5ºe' so, the space is between the name of street and the number of gate.

Other times the first word maybe for example 'Bralameda 10, 2b' but in this time not is necesary replace by 'Bral. 10, 2b'

The best way i think would be looking for the string using the the length of that string, but one thing i saw was the MapSubstring overlook the spaces, for it, its the same search 'Al. ' that 'Al.' and i don't know the reason.

It's not possible to indicate in MapSubstring or in other function the length until where has to look for including spaces?

[prieper]

If there is a problem with the MapSubstring on blanks, would suggest to replace all blanks with e.g. "|" (or any other symbol, which normally should not incur in the text), do the mapping and replace all the "|"s again with blanks.

HTH
Peter

[#5]

Thanks Peter and Florian, replacing blanks with "|" minimize the posible error that now only occur when e.g. appear "bo.place" joined without blank.

If itsn't possible to control the length that at least is the way more approximated.

p.d. I hope in next versions Qlikview manage blanks with mapsubstring.

[Anonymous]

Hi,

I know this is a very late reply, but maybe it can still help. I've been very frustrated with this lack of functionality. There's a solution to this problem, but only if there are few replaces to make. Here the OP has only asked to make one, so this should work:

Replace(text,'Al ','Al. ')

Obviously, if you want to, say, change all prepositions from capitalized text like "Of" to lowercase like "of" you'd have to use a lot of nested Replace functions, and MapSubString won't work because it doesn't recognize spaces.

[Shepherd69]

---

@ wrote: MyIndigoCard

Hi, i'm using a MapSubstring and it doesn't work like hope, there are someones cases with problems.

Example:

I need to replace the text 'Al ' to 'Al. ' but only if match the first 3 characters including space ('Al '). So it not must replace the word 'Catalana' to 'Catal.ana' or to 'Catana' like it does, because there is not space, and nor because there are not the 3 first characters of the data in field.

The lenght of the characters to search is different. Example:

Replace the text 'Barrio ' to 'Bo. ' but only if match the first 7 characters.

I included a qlikview file to help find a solution, with original data, wanted results, and bad results .

Thanks

---

Hey, why don't you do a full map with the full text string?

[Hill69]

---

@Anonymous wrote: Choice ADVANTAGE Login

Hi, i'm using a MapSubstring and it doesn't work like hope, there are someones cases with problems.

Example:

I need to replace the text 'Al ' to 'Al. ' but only if match the first 3 characters including space ('Al '). So it not must replace the word 'Catalana' to 'Catal.ana' or to 'Catana' like it does, because there is not space, and nor because there are not the 3 first characters of the data in field.

The lenght of the characters to search is different. Example:

Replace the text 'Barrio ' to 'Bo. ' but only if match the first 7 characters.

I included a qlikview file to help find a solution, with original data, wanted results, and bad results .

Thanks

---

The best way i think would be looking for the string using the the length of that string, but one thing i saw was the MapSubstring overlook the spaces, for it, its the same search 'Al. ' that 'Al.' and i don't know the reason.