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

[#1]

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);

[rajeshvaswani77]

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

thanks,

Rajesh Vaswani

[#3]

Hi,

Try this expression :

Left(Postcode, 2)

Is it what you're looking for ?

Regards,

Nicolas

[fdelacal]

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

[#5]

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

[rbecher · Accepted solution]

Why not use similar expression in QlikView:

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

- Ralf

[#7]

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

[rbecher]

Does my suggestion not work?

[#9]

Hello Ralf, it works

[rbecher]

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