Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Thanks for the quick reply Deepak
I am trying to do this in the script rather than as an expression. Any ideas??
Thanks
The same function will work in your script. E.g.,
left(subfield(Postcode,' ',1),len(subfield(Postcode,' ',1))) as AreaCode,
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
Ahhh found out what was wrong, had an incorrect setting in my set up.
It works now
Thanks everyone for your help
Cheers
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
Thanks Rocky
It was the prceding load button that wasn't ticked
All working a treat now
Cheers