Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
rbecher
Luminary Alumni
Luminary Alumni

Why not use similar expression in QlikView:

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

- Ralf

Vizlib Head of R&D

View solution in original post

10 Replies
rajeshvaswani77
Specialist III
Specialist III

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

thanks,

Rajesh Vaswani

Not applicable
Author

Hi,

Try this expression :

Left(Postcode, 2)

Is it what you're looking for ?

Regards,

Nicolas

fdelacal
Specialist
Specialist

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

Not applicable
Author

Capture1.JPG.jpg

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
Luminary Alumni
Luminary Alumni

Why not use similar expression in QlikView:

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

- Ralf

Vizlib Head of R&D

View solution in original post

Not applicable
Author

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

rbecher
Luminary Alumni
Luminary Alumni

Does my suggestion not work?

Vizlib Head of R&D
Not applicable
Author

Hello Ralf, it works

rbecher
Luminary Alumni
Luminary Alumni

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

Vizlib Head of R&D