Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Remove part of a string

Hi

I have a addressfield that include specific apartment-ID. I would like to remove the nr/id in the script.

The addresses can look like this.

Mainstreet 43 D lgh 2005  - remove D lgh 2005

Walkingstreet 9 lgh 105    - remove lgh 105

Littlestreet 10a, lgh 100    - remove a, lgh 100

Main littlestreet 240-10     - remove -10

Littlestreeet 52                  - don´t remove anything

Little mainstreet 12          - don´t remove anything

Hope you understand, I would like to keep the streetname and streetnr

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Hope that somebody can suggest a simpler way, by then the following works (Assuming, you have three digit street number at max):

Load

          Left (Address, RangeMax(findOneOf(Address, '0123456789'), If(FindOneOf(Address, '0123456789' ,2) - FindOneOf(Address, '0123456789') =1, If(FindOneOf(Address,'0123456789', 3) - findOneOf(Address, '0123456789',2)))))

AS NesAddress

From <>;

View solution in original post

14 Replies
rustyfishbones
Master II
Master II

try this

2014-03-04_0918.png

rustyfishbones
Master II
Master II

PFA qvw that may help

Regards

Alan

sunilkumarqv
Specialist II
Specialist II

Make use of somthing like this

subfield(FieldName, ' ', 1) & ' ' & subfield(FieldName, ' ', substringcount(FieldName, ' ')) as FieldName

Hope you get the solution

alexandros17
Partner - Champion III
Partner - Champion III

Hi,

use "findoneof(myString, '1234567890')" function to search the first numerical occurrence.

At this point use mid function to extract the substring starting from the first numerical occurrence, take the first n numerical values (you have to check any position in the string - I think no more than 4 digits) and at the end

use left of the original string up to the last numerical position

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Still alphabetic residues at the end of some addresses, Alan.

How about this?

SUBFIELD(Address, ' ', 1) & ' ' & KEEPCHAR(SUBFIELD(SUBFIELD(Address, ' ', 2), '-', 1), '0123456789')

beeaj2011
Contributor III
Contributor III

Hi

you can use a multiple (preceding loads) usage of subfield see attached qvw

Regards

Ton

Dropbox - split.qvw

rustyfishbones
Master II
Master II

Sorry I missed that.

regards

Alan

Not applicable
Author

Please try this:

mid(Address,1,findoneof(Address, '1234567890')-1) & ''& KeepChar(mid(Address,findoneof(Address, '1234567890'),4),'1234567890')

where Address is the Field

Help taken from Alessandro Saccone

Thanks,

Angad

alexandros17
Partner - Champion III
Partner - Champion III

Use this script (in a text box for instance ...) where myStr is your string

=If(FindOneOf(myStr,'1234567890')>0,

  If(IsNum(Mid(myStr,FindOneOf(myStr,'1234567890')+1,1)),

  If(IsNum(Mid(myStr,FindOneOf(myStr,'1234567890')+2,1)),

  'keep searching'

  ,Left(myStr,FindOneOf(AAA,'1234567890')+1)

  )

  ,Left(myStr,FindOneOf(myStr,'1234567890'))

  )

  ,myStr

)