topic AW:MapSubstring in left X characters in QlikView

MapSubstring in left X characters

Sun, 20 Feb 2011 13:21:35 GMT

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

AW:MapSubstring in left X characters

Sun, 20 Feb 2011 13:32:15 GMT

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

AW:MapSubstring in left X characters

Sun, 20 Feb 2011 16:36:21 GMT

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?

Re:AW:MapSubstring in left X characters

prieper — Sun, 20 Feb 2011 20:49:55 GMT

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

Re:AW:MapSubstring in left X characters

Sun, 20 Feb 2011 21:41:53 GMT

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.

Re: MapSubstring in left X characters

Anonymous — Tue, 30 Aug 2016 16:21:14 GMT

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.

Re: MapSubstring in left X characters

Shepherd69 — Wed, 23 Feb 2022 05:45:45 GMT

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

Re: MapSubstring in left X characters

Hill69 — Fri, 25 Feb 2022 04:22:45 GMT

@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