topic Re: Sub String a UK Postcode in a script in QlikView

Sub String a UK Postcode in a script

stuwannop — Wed, 14 Dec 2011 11:53:31 GMT

Hi Everyone

I have a geocodes table that I want to join up to a customer table (which has a UK postcode). My goecode table only contains the first half of theUK postcode (i.e. BB5 or AB24). In my script I need to substring the Postcode field in my customer table. Does someone know how to do this?

I need to bear in mind that I don't always need tyhe first x characters as string I need could be anything from 2 to 4 characters long.

Thanks for any help.

Stu

Re: Sub String a UK Postcode in a script

swuehl — Wed, 14 Dec 2011 12:35:40 GMT

Try something like

left( Postcode, ceil(len(Postcode)/2) ) as PostCodeGeoCodeMatch,

instead of ceil() function, you might need to use floor() depending on how the "first half" of Postcodes in the geocode table is determined when the number of characters is uneven.

Alternatively, you could try using one of the match functions or a mapsubstring together with a lookup table (try using a lookup table with two times the geocode's postcodes field).

Hope this helps,

Stefan

Sub String a UK Postcode in a script

swuehl — Wed, 14 Dec 2011 12:39:54 GMT

Ah, UK postcodes have a space as delimiter, right? And you need the first part only?

subfield( Postcode, ' ', 1) as PostCodeGeoCodeMatch

Sub String a UK Postcode in a script

stuwannop — Wed, 14 Dec 2011 12:44:02 GMT

Thanks Stefan

It looks like both the solutions you offered work. I went with the first option

left( Postcode, ceil(len(Postcode)/2) )) as PostCodeGeoCodeMatch

Now just need to work out google maps!

Help much appreciated.

Stu