

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
split up an address into parts
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?
- Tags:
- qlikview_scripting
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |);

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
worked like a charm, thanks Aar


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oops, missed Country

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
subfield(full_address,',',3) as Country
only for Australia though
subfield(full_address,',',4) as Country for US

- « Previous Replies
-
- 1
- 2
- Next Replies »