Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Remove last 3 digits from UK postcode

I would like to import postcode field in a table but only import the first section of the UK postcode.

UK postcodes begin with either 1 or two letters, have a single or double digit number and then end with a number and 2 letters. E.g:

LN NLL or LLN NLL or LLNN NLL or LNN NLL (they won't always have a space between).

I'm only interested in inputting the first characters (everything before the last 3 digits - LN, LLN, LLNN, LNN).

Is there a way of doing this as a calculated field when loading a table?

1 Solution

Accepted Solutions
awhitfield
Esteemed Contributor

Re: Remove last 3 digits from UK postcode

Hi Simon,

try out:


LOAD Left(PostCode, Len(PostCode)-3) as PostCode

FROM

(
ooxml, embedded labels, table is Sheet1);

Andy

6 Replies

Re: Remove last 3 digits from UK postcode

Could you give an example on real postcodes?

- Marcus

MVP
MVP

Re: Remove last 3 digits from UK postcode

May be try this:

LOAD Trim(Left(PostCodes, Len(PostCodes) - 3)) as PostCodes

awhitfield
Esteemed Contributor

Re: Remove last 3 digits from UK postcode

Hi Simon,

try out:


LOAD Left(PostCode, Len(PostCode)-3) as PostCode

FROM

(
ooxml, embedded labels, table is Sheet1);

Andy

Re: Remove last 3 digits from UK postcode

If there is no space between the first and second part of post code, there is no way we can achieve the exact output.

In case of space we can use

SubField(PostCode,' ',1) as FirstPartofPostCode

Not applicable

Re: Remove last 3 digits from UK postcode

Works a treat, I tweaked it and put it into calculated Field expression.

Thanks for your help everybody - really appreciate it.

MVP
MVP

Re: Remove last 3 digits from UK postcode

You might want to consider adding Trim in there to make sure you don't have the extra white space at the end in those scenarios where there is a space between the code and 3 following letters