Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

MapSubstring in left X characters

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

7 Replies
Not applicable
Author

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



Not applicable
Author

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
Master II
Master II

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

Not applicable
Author

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.

qlinikalanalyst
Contributor III
Contributor III

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
Contributor III
Contributor III


@ 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
Contributor II
Contributor II


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