topic Re: Ignore text after a special character in QlikView

Ignore text after a special character

Thu, 27 Sep 2012 15:44:19 GMT

Hi everyone,

I have a string which is a persons address, often this ends with a postcode ....AB11AB, or something the postcode will be AB121AB. I used an expression found on here to extract the postcode if it is the last part of the string:

REPLACE(

TRIM

(right(PatientFullAddress,7) &

subfield

(subfield

(PatientFullAddress,right

(PatientFullAddress,7)

,-1)

,' ','')

)

,' ','') as PatientPostcode2,

left(subfield(Postcode,' ',1),len(subfield(Postcode,' ',1)))

But I have found the address string sometimes has AB11AB : house number (Insert a number here for this example).

How can I use the above but exclude all of the text after the ':', or is there a way to improve what I have already?

Many thanks

Re: Ignore text after a special character

matt_crowther — Thu, 27 Sep 2012 16:08:39 GMT

To isolate everything prior to the ':' I'd look to use something in the load script like:

left(MyValues,index(MyValues,':')-2) as MyVlaues

That assumes you have a space between the end of the PostCode and the ':'.

Obviously replace 'MyValues' with your text string.

Hope that helps,

Matt - Visual Analytics Ltd

Qlikview Design Blog: http://QVDesign.wordpress.com

@QlikviewBI

Re: Ignore text after a special character

Fri, 28 Sep 2012 11:24:29 GMT

Hi Matt, thanks for teh reply. I could only get this to work on those records where the address contains ':'. I would ike it to include the other entries as well e.g.

anystreet AB11YZ: 9876

anyplace AB114NB

the above would turn out: -

AB11YZ

AB114NB

Many thanks