topic Re: split up an address into parts in QlikView

split up an address into parts

alan_richmond — Wed, 15 Feb 2017 20:24:09 GMT

Hi I am looking for the best way in a load script to split up an address to fields of its parts.

as an example a single address field of .. full_address of 1600 Amphitheatre Pkwy, Mountain View, CA 94043, USA

to be split up to

Country of USA

Zipcode of 94043

State of CA

City of Mountain View

Address of 1600 Amphitheatre Pkwy

Is subfield the best way or right or left or mid?

Re: split up an address into parts

sunny_talwar — Wed, 15 Feb 2017 20:32:37 GMT

May be SubField with comma as delimiters

SubField(Address, ',', 1) as Address,

SubField(Address, ',', 2) as City,

SubField(SubField(Address, ',' , 3), ' ', 1) as State,

SubField(SubField(Address, ',' , 3), ' ', 2) as ZipCode,

SubField(Address, ',', 4) as Country

The above heavy rely on the data being consistent.

Re: split up an address into parts

aarkay29 — Wed, 15 Feb 2017 20:38:26 GMT

Try this

subfield(full_address,',',1) as Address

subfield(full_address,',',2) as City

subfield(full_address,',',4) as Country

Purgechar(subfield(full_address,',',3),'0123456789') as State

keepchar(subfield(full_address,',',3),'0123456789') as ZIP

Re: split up an address into parts

alan_richmond — Wed, 15 Feb 2017 20:44:52 GMT

Hmm,

I also have Australian address like ..

1-2 Main St, Box Hill VIC 3128, Australia

where there is not a comma after the City

Re: split up an address into parts

sunny_talwar — Wed, 15 Feb 2017 20:49:25 GMT

I guess if you can provide 10-15 lines of different variation of address, we might be able to provide you with a sample which might work across all the different addresses

Re: split up an address into parts

alan_richmond — Wed, 15 Feb 2017 20:57:31 GMT

Both are the output of the googleapis geocoder so it looks like all the Australian addresses miss the comma after city and the US addresses use the comma.

the Australian zipcode is 3 or 4 numbers and the state is always 2 or three characters and there are only 8 possible state names

Re: split up an address into parts

aarkay29 — Wed, 15 Feb 2017 21:15:21 GMT

How about this, Only works for Australia use it in a if statement

subfield(full_address,',',1) as Address,

PurgeChar(Purgechar(subfield(full_address,',',2),'123456789'),Right(Trim(Purgechar(subfield(full_address,',',2),'123456789')),3)) as City,

Right(Trim(Purgechar(subfield(full_address,',',2),'123456789')),3) as State,

Keepchar(subfield(full_address,',',2),'123456789') as Zip,

Re: split up an address into parts

alan_richmond — Wed, 15 Feb 2017 21:22:55 GMT

worked like a charm, thanks Aar

Re: split up an address into parts

alan_richmond — Wed, 15 Feb 2017 21:28:27 GMT

Oops, missed Country

Re: split up an address into parts

aarkay29 — Wed, 15 Feb 2017 21:36:29 GMT

subfield(full_address,',',3) as Country

only for Australia though

subfield(full_address,',',4) as Country for US

Re: split up an address into parts

alan_richmond — Wed, 15 Feb 2017 23:40:51 GMT

Hi Aar,

looks like spaces in city names are causing issues and the purge is removing characters in city names

Below is the current code and attached is a sample of the output

,subfield(formatted_address,',',1) as Address

,PurgeChar(PurgeChar(subfield(formatted_address,',',2),'123456789'),Right(trim(Purgechar(subfield(formatted_address,',',2),'123456789')),3)) as City

,Right(Trim(Purgechar(subfield(formatted_address,',',2),'123456789')),3) as State

,Keepchar(subfield(formatted_address,',',2),'123456789') as Postcode

,subfield(formatted_address,',',3) as Country

Re: split up an address into parts

sunny_talwar — Thu, 16 Feb 2017 03:38:28 GMT

Try this:

Table:

LOAD Trim(SubField(Address, ',', 1)) as Address,

If(Trim(SubField(Address, ',', -1)) = 'USA', Trim(SubField(Address, ',', 2)), Trim(Left(SubField(Address, ',', 2), Index(Trim(SubField(Address, ',', 2)), ' ', -2)))) as City,

If(Trim(SubField(Address, ',', -1)) = 'USA', Trim(SubField(Trim(SubField(Address, ',', 3)), ' ', 1)), Trim(SubField(SubField(Address, ',', 2), ' ', -2))) as State,

If(Trim(SubField(Address, ',', -1)) = 'USA', Trim(SubField(Trim(SubField(Address, ',', 3)), ' ', 2)), Trim(SubField(SubField(Address, ',', 2), ' ', -1))) as ZipCode,

Trim(SubField(Address, ',', -1)) as Country;

LOAD * Inline [

Address

1600 Amphitheatre Pkwy, Mountain View, CA 94043, USA

1-2 Main St, Box Hill VIC 3128, Australia

] (delimiter is |);