can anyone help to extract 5 digit zipcode from an address

[ratnadeep]

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

[panosalexand]

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

[Gabbar]

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. 

[Ahidhar]

try this if 

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

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

[Parthiban]

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]

Right(Full_Address, 5)

//

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