topic Re: ZIP Code Formats Part 2 in QlikView

ZIP Code Formats Part 2

gfisch13 — Sat, 16 Nov 2024 19:45:02 GMT

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.

Re: ZIP Code Formats Part 2

Arthur_Fong — Wed, 06 Nov 2019 00:09:45 GMT

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

Re: ZIP Code Formats Part 2

gfisch13 — Wed, 06 Nov 2019 19:43:11 GMT

Thanks again Arthur - exactly what I was looking for.