topic Re: UK Postcode Formatting in QlikView

UK Postcode Formatting

Thu, 08 Oct 2015 14:37:21 GMT

Hi,

I hope someone is able to help me with my query.

I have a large amount of data I need to feed into Qlikview and i'm having issues formatting the Postcode field. What I need is to convert a full postcode into a 5 character code. As UK postcodes come in all different shapes and sizes this is proving to be a little bit trickier than I first envisaged.

I'll try and show examples of what i'm looking for below.

Original Postcode 5 Character Version (The format I need)

LL32 8AJ LL328

BB7 3ER BB7 3 (Includes 1 space)

L1 8EE L1 8 (Includes 2 spaces)

IP12 2UE IP122

W1W 5NU W1W 5 (Includes 1 space)

I hope this gives a good idea of exactly what i'm after?

Cheers,

Paul

Re: UK Postcode Formatting

MarcoWedel — Thu, 08 Oct 2015 21:53:16 GMT

Hi,

one solution could be:

tabPostcodes:
LOAD *,
    SubField(Postcode,' ',1)&Repeat(' ',4-Len(SubField(Postcode,' ',1)))&Left(SubField(Postcode,' ',2),1) as Postcode2
Inline [
Postcode
LL32 8AJ
BB7 3ER
L1 8EE
IP12 2UE
W1W 5NU
];

hope this helps

regards

Marco

Re: UK Postcode Formatting

Fri, 09 Oct 2015 07:42:09 GMT

Hi Marco,

Thank you ever so much for your assistance. The code provided works perfectly

Cheers,

Paul

Re: UK Postcode Formatting

MarcoWedel — Sat, 10 Oct 2015 10:35:21 GMT

‌You're welcome.

regards

Marco