ZIP Code Formats Part 2

[gfisch13]

Hi all - I have found the following condition in my data.  the original function worked, but I found a situation where the Zip field had 8 digits, where there should have been 9, which means that the leading 0 has been dropped.

my Zip field is reading:   77510000

I want to get, which is the proper zip code:   07751

I've used this function in my LOAD statement.  My data loads from QVD's.  While the script runs all the way through the results in my field are either 0 or -1.  (There are additional conditions to deal with other formats in the data, the data was not very clean).

if(len(EMPE_ZIP_CD)<5,num(EMPE_ZIP_CD, '00000'),if(len(EMPE_ZIP_CD)>5,left(EMPE_ZIP_CD,5), EMPE_ZIP_CD)) or
if(len(EMPE_ZIP_CD)=8,left(num#(EMPE_ZIP_CD,'00000'),4), EMPE_ZIP_CD) as EE_Zip

Would really appreciate any additional help.

[Arthur_Fong · Accepted solution]

Try this:

Raw:
load
if(len(PostCode)<5,num(PostCode,'00000'),
if(len(PostCode)>5 and len(PostCode)<>8,left(PostCode,5),
if(len(PostCode)>5 and len(PostCode)=8,left(num(PostCode,'000000000'),5),
PostCode)))as PostCode
inline [
PostCode
123450000
34567
7751
77510000
];

[gfisch13]

Thanks again Arthur - exactly what I was looking for.