topic Re: Remove part of a string in QlikView

Remove part of a string

Tue, 04 Mar 2014 08:54:35 GMT

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

Re: Remove part of a string

rustyfishbones — Tue, 04 Mar 2014 09:18:52 GMT

try this

Re: Remove part of a string

rustyfishbones — Tue, 04 Mar 2014 09:20:20 GMT

PFA qvw that may help

Regards

Alan

Re: Remove part of a string

sunilkumarqv — Tue, 04 Mar 2014 09:25:01 GMT

Make use of somthing like this

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

Hope you get the solution

Re: Remove part of a string

alexandros17 — Tue, 04 Mar 2014 09:26:50 GMT

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

Re: Remove part of a string

Peter_Cammaert — Tue, 04 Mar 2014 09:30:44 GMT

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

How about this?

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

Re: Remove part of a string

beeaj2011 — Tue, 04 Mar 2014 09:34:04 GMT

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

Regards

Ton

Dropbox - split.qvw

Re: Remove part of a string

rustyfishbones — Tue, 04 Mar 2014 09:36:16 GMT

Sorry I missed that.

regards

Alan

Re: Remove part of a string

Tue, 04 Mar 2014 09:39:10 GMT

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

Re: Remove part of a string

alexandros17 — Tue, 04 Mar 2014 09:41:48 GMT

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

)

Re: Remove part of a string

tresB — Tue, 04 Mar 2014 09:56:20 GMT

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 <>;

Re: Remove part of a string

Tue, 04 Mar 2014 10:33:20 GMT

This is truly awesome. gone for a hour and so many tips! I will start testing, thank you all!

Re: Remove part of a string

Tue, 04 Mar 2014 10:39:02 GMT

try this hope this helps you.

SubField(Name,' ',1 ) & ' ' & if(SubStringCount(Name,' ') <=2, subfield(Name, ' ' ,2) &' '& subfield(Name, ' ' ,3),subfield(Name, ' ' ,2)) as Address;

Re: Remove part of a string

Tue, 04 Mar 2014 10:58:43 GMT

seems to do the trick!

Re: Remove part of a string

Tue, 04 Mar 2014 11:40:50 GMT

Hi Hofstedt ,

PFA.

hope this may help u