topic Re: How do i get the two letters of a post code using expression? in QlikView

How do i get the two letters of a post code using expression?

Wed, 28 Aug 2013 14:24:10 GMT

Hello All,

I know am a pain but please bear with me.

what i am trying to achieve is to get the first two letters of a post code in a city and then i want to create an analysis of post codes where accident is more frequent.

below is the address table; what i want is get the first two letters in the postcode then i can use Towncity as a dimension and can drill through the post code, i dont know if am making sense.. any idea will be appreciated

Address:

LOAD AddressID,

// FKCountryID,

// Address1,

// Address2,

TownCity,

County,

Postcode

// Latitude,

// Longitude,

// FullAddress,

// Deleted,

// LastUpdatedDate,

// CreatedDate

FROM

Tier1_QVDs\Address.qvd

(qvd);

Re: How do i get the two letters of a post code using expression?

rajeshvaswani77 — Wed, 28 Aug 2013 14:26:45 GMT

For the first part of the question you could use left(postcode,2). I am not sure about the second part.

thanks,

Rajesh Vaswani

Re: How do i get the two letters of a post code using expression?

Wed, 28 Aug 2013 14:26:58 GMT

Hi,

Try this expression :

Left(Postcode, 2)

Is it what you're looking for ?

Regards,

Nicolas

Re: How do i get the two letters of a post code using expression?

fdelacal — Wed, 28 Aug 2013 14:31:35 GMT

the 2 leter alwways be first???

if yes you can do left(Postcode,2) as letter_poscode

If the letter are for anywuere you can use

example:

purgechar ( 'a1b2c3','123' ) return 'abc' ..

purgechar ( 'Postcode','1234567890' ) as letter_poscode

Hope that gelps you

Re: How do i get the two letters of a post code using expression?

Wed, 28 Aug 2013 14:58:56 GMT

Hello, basically this is what i need, i can do it on sql but how do i update it in the address table i have in the folder.

I want to add this line of code to the address table but how can i do it. You guys rock

CASE (isnumeric(SUBSTRING([Postcode] , 2 , 1))) WHEN 1 THEN LEFT ([Postcode] , 1) ELSE LEFT ([Postcode] , 2) END as PostcodeLetters

Re: How do i get the two letters of a post code using expression?

rbecher — Wed, 28 Aug 2013 15:20:21 GMT

Why not use similar expression in QlikView:

LOAD if(isnum(mid([Postcode] , 2 , 1)), lef([Postcode] ,1), left([Postcode] ,2)) as PostcodeLetters, ...

- Ralf

Re: How do i get the two letters of a post code using expression?

Thu, 29 Aug 2013 08:44:50 GMT

Thanks all, I really do appreciate. I am getting there gradually

Re: How do i get the two letters of a post code using expression?

rbecher — Thu, 29 Aug 2013 08:48:54 GMT

Does my suggestion not work?

Re: How do i get the two letters of a post code using expression?

Thu, 29 Aug 2013 09:11:10 GMT

Hello Ralf, it works

Re: How do i get the two letters of a post code using expression?

rbecher — Thu, 29 Aug 2013 09:45:42 GMT

Then mark it so. This gives other community members the hint what is the solution of this question.

Re: How do i get the two letters of a post code using expression?

kelly_oakley — Wed, 30 Sep 2020 14:12:18 GMT

Hi, what would this expression be to produce the first half of the postcode?