topic Re: Zip Code Formats in QlikView

Zip Code Formats

gfisch13 — Sat, 16 Nov 2024 19:46:36 GMT

Hi everyone -

I have a Zip Code field in my data that has inconsistent data. I need to parse several formats but I'm not sure how to go about it.

the field is a 9 character text field, my formats look like the following:

12345

123450000

12345

7751 (this one drops a leading zero, its 07751 in NJ)

I'm thinking I need some type of conditional, but I'm not sure of the functions to use, nor do I understand how to place this in the load script. My goal is to end up with just the 5 digits of the zip code.

Any input is appreciated - thanks!

Re: Zip Code Formats

Arthur_Fong — Thu, 31 Oct 2019 23:45:14 GMT

Try this:

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

Re: Zip Code Formats

gfisch13 — Fri, 01 Nov 2019 15:20:37 GMT

This worked perfectly - thanks Arthur!

Re: Zip Code Formats

gfisch13 — Tue, 05 Nov 2019 19:27:49 GMT

Arthur - Here's an additional question that stems from a deeper review of the data. This 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.

Can I insert additional conditions into what you sent to account for this?

my Zip field is reading: 77510000

when I use the function you provided I get: 77510, which is correct based on your programming but not correct based on what the value should be.

I should get: 07751

So hence my question of adding additional conditions.

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.

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.

Thanks!

Re: Zip Code Formats

Arthur_Fong — Tue, 05 Nov 2019 23:52:44 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
];