How to get length of field before 1st space

[#1]

Hi

I am trying to use Postcode lookups to add an area to the data I am analysing.

If I use Left string I get most of the fields matched up but I need to remove the text after the space in the post code to get a full match.

Eg Left 4 on N1 4TH will not match to N1 as it is coming over as N1 4 which isn''t a match to my postcode lookup

If I use Left 3 I will not match the postcodes that are 4 digits eg NN44

Anyone know how to strip the text after the 1st blank space...I am thinking of using length upto the space then using this as the left statement.

Thankyou for any assistance you can offer.

cheers

[deepakk]

Hi,

I hope this expression helps you out.

=left(subfield(vNewPassword,' ',1),len(subfield(vNewPassword,' ',1)))

where vNewPassword would be the field or variable name.

[#3]

Thanks for the quick reply Deepak

I am trying to do this in the script rather than as an expression. Any ideas??

Thanks

[colinh]

The same function will work in your script. E.g.,

left(subfield(Postcode,' ',1),len(subfield(Postcode,' ',1))) as AreaCode,

[#5]

Thanks Colin

I thought it would work as a script but when I run I get the following error.

SQL Error: 'subfield' is not a recognised built in function name

Is this a version uissue I am running version 9 not the latest.

Regards

[#6]

Ahhh found out what was wrong, had an incorrect setting in my set up.

It works now

Thanks everyone for your help

Cheers

[boorgura]

I think you are trying the to use the Qlikview function in the sql script.

Try this:

LOAD fieldnames,

subfield(...)

SQL Select fieldnames

from table;

The preceding LOAD should solve your issue I believe

[#8]

Thanks Rocky

It was the prceding load button that wasn't ticked

All working a treat now

Cheers