Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
ratnadeep
Contributor II
Contributor II

can anyone help to extract 5 digit zipcode from an address

I have a column for Full_Address e.g e.g 1 W Pratt St, Baltimore, MD 21201, United States but i want to extract and create a separate column for ZipCode.

Note : ZipCode will not essentially be at fixed position in all the  addresses

Labels (1)
6 Replies
panosalexand
Creator
Creator

Hi ,so which part of this full address you need to keep as zipcode?  MD 21201?

Gabbar
Specialist
Specialist

There is not a sure Solution for this because there is no way to identity the code in Qlik.

but Some functions of combination might work like:-

A:
Load replace(Address,' ','-') as Address;
Load * Inline [
Address
1 W Pratt St, Baltimore, MD 21201, United States
](delimiter is :);

 

NoConcatenate
B:
Load KeepChar(SubField(Address,'-'),'1234567890') as Address Resident A
//where len(KeepChar(SubField(Address,'-'),'1234567890')) = '5'
;

NoConcatenate
C:
Load * Resident B
where Len(Address) = 5;

Drop Table B;

Drop Table A;


Anil_Babu_Samineni

If you have your local Zip code details already that are connected to town, you can map that with other table and with that reference load from mapping table. With this, It always look that are from your address with identify the city and get the region code. Just an idea. 

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Ahidhar
Creator III
Creator III

try this if 

zip code - 1 W Pratt St, Baltimore, MD 21201, United States

right(subfield(zip code,' , ' , 3), 5)

Parthiban
Creator
Creator

Hi,

Try keepchar function and then Right  function of 5 then u will have zipcode from this address.

Right(KeepChar(Address,'1234567890'),5).

This will work for you.

Aasir
Creator III
Creator III

Right(Full_Address, 5)

//

LOAD
Full_Address,
Right(Full_Address, 5) as ZipCode
FROM [your data source];