topic postcode validation in QlikView

postcode validation

Thu, 08 Jul 2010 16:19:20 GMT

i'm looking for a way of validating a postcode in load script.

i found this on the cabinet office data standards catalogue: (http://www.cabinetoffice.gov.uk/govtalk/schemasstandards/e-gif/datastandards/address/postcode.aspx)

<xsd:simpleType name="PostCodeType">

<xsd:annotation>

<xsd:documentation>complex pattern for postcode, which matches definition, accepted by some parsers is: "(GIR 0AA)|((([A-Z-[QVX]][0-9][0-9]?)|(([A-Z-[QVX]][A-Z-[IJZ]][0-9][0-9]?)|(([A-Z-[QVX]][0-9][A-HJKSTUW])|([A-Z-[QVX]][A-Z-[IJZ]][0-9][ABEHMNPRVWXY])))) [0-9][A-Z-[CIKMOV]]{2})"</xsd:documentation>

</xsd:annotation>

<xsd:restriction base="xsd:string">

<xsd:pattern value="[A-Z]{1,2}[0-9R][0-9A-Z]? [0-9][A-Z-[CIKMOV]]{2}"/>

</xsd:restriction>

</xsd:simpleType>

was just wondering if anyone had any suggestions for this?

Cheers

Rick

postcode validation

Thu, 08 Jul 2010 16:36:25 GMT

It's a regular expression for a postcode validation in UK. This one is more detailed and accurate:

GIR 0AA)|((([A-Z-[QVX]][0-9][0-9]?)|(([A-Z-[QVX]][A-Z-[IJZ]][0-9][0-9]?)|(([A-Z-[QVX]][0-9][A-HJKSTUW])|([A-Z-[QVX]][A-Z-[IJZ]][0-9][ABEHMNPRVWXY])))) [0-9][A-Z-[CIKMOV]]{2})

This one is simpler:

[A-Z]{1,2}[0-9R][0-9A-Z]? [0-9][A-Z-[CIKMOV]]{2}

There is no internal function in QV for regular expressions. You can use VBScript macros or use a database function at dataload time in QV script.

Remember in other countries there are other rules for post/zip codes.

postcode validation

johnw — Thu, 08 Jul 2010 20:55:09 GMT

A couple possible solutions in this thread, though it looks like the definition of a post code is a little different than I was told over there. Hopefully it can be modified to suit. The idea is brute force, just specifying the allowed characters for each position.

http://community.qlik.com/forums/t/30223.aspx


John Witherspoon wrote: I'm honestly not thinking of a better way than pure brute force:
if( index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,1,1))
 and index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,2,1))
 and index('0123456789' ,mid(postcode,3,1))
 and index('0123456789' ,mid(postcode,4,1))
 and mid(postcode,5,1) = ' '
 and index('0123456789' ,mid(postcode,6,1))
 and index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,7,1))
 and index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,8,1)),postcode,'Error') as postcode
I mean, we could create fake functions, but I don't think it's worth the complexity. I think it would look something like this:
SET alpha = index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid($1,$2,1));
SET digit = index('0123456789',mid($1,$2,1));
SET space = mid($1,$2,1) = ' ';
if( $(alpha(postcode,1))
 and $(alpha(postcode,2))
 and $(digit(postcode,3))
 and $(digit(postcode,4))
 and $(space(postcode,5))
 and $(digit(postcode,6))
 and $(alpha(postcode,7))
 and $(alpha(postcode,8)),postcode,'Error') as postcode
It won't surprise me if there's a clever way to do it. I'm just not thinking of one.

postcode validation

Thu, 08 Jul 2010 21:17:19 GMT

The problem is that in UK they don't have a fixed format for postcodes. Here are some examples:
RG4 5EB
W1 1EV
SW1A 1AA

It looks like car plate numbers in US. But car plate numbers in UK have more strict format.

postcode validation

rwunderlich — Thu, 08 Jul 2010 23:29:52 GMT


Nick Bor wrote:There is no internal function in QV for regular expressions. You can use VBScript macros

The QV Cookbook has a sampled named "Regular Expression pattern matching function" that shows how to use RegEx to validate US Phone numbers. Similar exercise if you want to use that as a starting point.

-Rob

postcode validation

Fri, 09 Jul 2010 01:05:42 GMT


Rob Wunderlich wrote:The QV Cookbook has a sampled named "Regular Expression pattern matching function"

Where can I find it?

postcode validation

rwunderlich — Fri, 09 Jul 2010 22:31:50 GMT

You can download the Cookbook from

http://robwunderlich.com/Download.html

-Rob

postcode validation

Wed, 14 Jul 2010 09:35:49 GMT

thanks guys used a combination of all the answers given,

used the first regex suggested but couldnt get the hang of the syntax so used the Cookbook to help me.

thanks guys.