topic Re: How t separate postcode from complete address field? in QlikView

How t separate postcode from complete address field?

Mon, 21 Sep 2015 09:01:29 GMT

Hi,

I have address field as below in table

4 Mountview, Keady, wards HJ60 3RG

34 Wyndell Heights, wards HJ23 7GX

234 Bawnmore Park, wards HJ36 7BQ

56 Thornbrooke, Ahoghill, Toristreet, HJ42 1PZ

Woodlands, 2 Bachelors, Portadown, wards HJ63 5BQ

90 Leven Park, Huyyijane, HJ5 7JA

98 Dunsuivnish Avenue, wards HJ55 7EP

5e Castle Hill, Toristreet, HJ70 1JP

23 Dunsuivnish Avenue, Toristreet, HJ55 7EP

78 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

12 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

2 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

11 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

67 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

43 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

45 Ballybeen Park, Dundonald, Huyyijane, HJ16 2QB

90 Abbey Ring, Holywood, HJ18 9NS

23 Abbey Ring, Holywood, HJ18 9NS

I need to separate Postcdoe from complete address. Always postcode starts with 'HJ'. how to separate postcode from entire address for each address value.

Please can anyone suggest me how to do this.

Thanks.

Re: How t separate postcode from complete address field?

sunny_talwar — Mon, 21 Sep 2015 09:03:18 GMT

May be this:

SubField(Address, ' ', -2) as PostCode

Left(Address, Len(Address) - 10) as Address

SubField(Address, ' ', -2) & ' ' & SubField(Address, ' ', -1) as PostCode,

Left(Address, Len(Address) - 9) as Address

Re: How t separate postcode from complete address field?

Mon, 21 Sep 2015 09:08:47 GMT

Thanks. when I used this it is showing only first half part of postcode

for eg: the complete postcode in below address is HJ60 3RG

4 Mountview, Keady, wards HJ60 3RG

but it is showing only HJ60

do I need to modify anything here?

Re: How t separate postcode from complete address field?

sunny_talwar — Mon, 21 Sep 2015 09:09:34 GMT

Script:

Table:

LOAD SubField(Address, ' ', -2) & ' ' & SubField(Address, ' ', -1) as PostCode,

Left(Address, Len(Address) - 9) as Address;

LOAD * Inline [

Address

4 Mountview, Keady, wards HJ60 3RG

34 Wyndell Heights, wards HJ23 7GX

234 Bawnmore Park, wards HJ36 7BQ

56 Thornbrooke, Ahoghill, Toristreet, HJ42 1PZ

Woodlands, 2 Bachelors, Portadown, wards HJ63 5BQ

90 Leven Park, Huyyijane, HJ5 7JA

98 Dunsuivnish Avenue, wards HJ55 7EP

5e Castle Hill, Toristreet, HJ70 1JP

23 Dunsuivnish Avenue, Toristreet, HJ55 7EP

78 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

12 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

2 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

11 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

67 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

43 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

45 Ballybeen Park, Dundonald, Huyyijane, HJ16 2QB

90 Abbey Ring, Holywood, HJ18 9NS

23 Abbey Ring, Holywood, HJ18 9NS

] (delimiter is |);

Re: How t separate postcode from complete address field?

sunny_talwar — Mon, 21 Sep 2015 09:11:18 GMT

Modified that above code. Please check again

Re: How t separate postcode from complete address field?

Mon, 21 Sep 2015 09:12:56 GMT

Great Thanks. just checked your modify script. for some of the address there is no postcode like below

109 Ashgrove Manor Killpo Donegal

in the Postcode filed for above address it is showing as Killpo Donegal

how to change the script to not enter into Postcode field when there is no postcod ein complete address? so I need the Postcode column should show blank when there is no Postcode.

would it be possible?

Re: How t separate postcode from complete address field?

sunny_talwar — Mon, 21 Sep 2015 09:19:25 GMT

Check this:

Table:

LOAD If(Left(SubField(Address, ' ', -2), 2) = 'HJ', SubField(Address, ' ', -2) & ' ' & SubField(Address, ' ', -1)) as PostCode,

If(Left(SubField(Address, ' ', -2), 2) = 'HJ', Left(Address, Len(Address) - 9), Address) as Address;

LOAD * Inline [

Address

4 Mountview, Keady, wards HJ60 3RG

34 Wyndell Heights, wards HJ23 7GX

234 Bawnmore Park, wards HJ36 7BQ

56 Thornbrooke, Ahoghill, Toristreet, HJ42 1PZ

Woodlands, 2 Bachelors, Portadown, wards HJ63 5BQ

90 Leven Park, Huyyijane, HJ5 7JA

98 Dunsuivnish Avenue, wards HJ55 7EP

5e Castle Hill, Toristreet, HJ70 1JP

23 Dunsuivnish Avenue, Toristreet, HJ55 7EP

78 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

12 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

2 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

11 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

67 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

43 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

45 Ballybeen Park, Dundonald, Huyyijane, HJ16 2QB

90 Abbey Ring, Holywood, HJ18 9NS

23 Abbey Ring, Holywood, HJ18 9NS

109 Ashgrove Manor Killpo Donegal

] (delimiter is |);

Re: How t separate postcode from complete address field?

Mon, 21 Sep 2015 09:24:31 GMT

Excellent. Thanks. one last request please when there is gap in between Postcode like below how to get these type of Postcodes into Postcode column

90 Railway Court, Dungiven, Yo Janerry HJ 34 4IU

Re: How t separate postcode from complete address field?

sasiparupudi1 — Mon, 21 Sep 2015 09:25:01 GMT

try

= 'HJ'&SubField(yourText,'HJ',2)

hth

Sasi

Re: How t separate postcode from complete address field?

sunny_talwar — Mon, 21 Sep 2015 09:32:48 GMT

Try this:

Table:

LOAD If(Left(SubField(Address, ' ', -2), 2) = 'HJ', SubField(Address, ' ', -2) & ' ' & SubField(Address, ' ', -1),

If(Left(SubField(Address, ' ', -3), 2) = 'HJ', SubField(Address, ' ', -3) & ' ' & SubField(Address, ' ', -2) & ' ' & SubField(Address, ' ', -1))) as PostCode,

If(Left(SubField(Address, ' ', -2), 2) = 'HJ', Left(Address, Len(Address) - 9),

If(Left(SubField(Address, ' ', -3), 2) = 'HJ', Left(Address, Len(Address) - 10), Address)) as Address;

LOAD * Inline [

Address

4 Mountview, Keady, wards HJ60 3RG

34 Wyndell Heights, wards HJ23 7GX

234 Bawnmore Park, wards HJ36 7BQ

56 Thornbrooke, Ahoghill, Toristreet, HJ42 1PZ

Woodlands, 2 Bachelors, Portadown, wards HJ63 5BQ

90 Leven Park, Huyyijane, HJ5 7JA

98 Dunsuivnish Avenue, wards HJ55 7EP

5e Castle Hill, Toristreet, HJ70 1JP

23 Dunsuivnish Avenue, Toristreet, HJ55 7EP

78 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

12 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

2 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

11 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

67 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

43 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

45 Ballybeen Park, Dundonald, Huyyijane, HJ16 2QB

90 Abbey Ring, Holywood, HJ18 9NS

23 Abbey Ring, Holywood, HJ18 9NS

109 Ashgrove Manor Killpo Donegal

90 Railway Court, Dungiven, Yo Janerry HJ 34 4IU

] (delimiter is |);

Re: How t separate postcode from complete address field?

sunny_talwar — Mon, 21 Sep 2015 09:34:47 GMT

I thought of doing it this way, but the only problem is that if there is another HJ in the SubField('...', 'HJ', 2) might not give you the right result.

Re: How t separate postcode from complete address field?

sasiparupudi1 — Mon, 21 Sep 2015 09:43:10 GMT

Hi Sunny

The address looks like in mixed case.. so it might not be unsafe to use HJ as a delimeter

Sasi

Re: How t separate postcode from complete address field?

Mon, 21 Sep 2015 09:43:15 GMT

Thanks. working.

Re: How t separate postcode from complete address field?

Mon, 21 Sep 2015 09:43:42 GMT

Thanks for your help.

Re: How t separate postcode from complete address field?

Mon, 21 Sep 2015 09:45:54 GMT

Thanks. working.

Re: How t separate postcode from complete address field?

d_koti24 — Mon, 21 Sep 2015 12:50:47 GMT

Hi,

you can try like this also,This is the simplest way.

address:

LOAD * INLINE [

address

"4 Mountview, Keady, wards HJ60 3RG",

"34 Wyndell Heights, wards HJ23 7GX",

"234 Bawnmore Park, wards HJ36 7BQ",

"56 Thornbrooke, Ahoghill, Toristreet, HJ42 1PZ",

"Woodlands, 2 Bachelors, Portadown, wards HJ63 5BQ",

"90 Leven Park, Huyyijane, HJ5 7JA",

"98 Dunsuivnish Avenue, wards HJ55 7EP",

"5e Castle Hill, Toristreet, HJ70 1JP",

"23 Dunsuivnish Avenue, Toristreet, HJ55 7EP",

"78 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL",

"12 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL",

"2 Newtown Road, Camlough, Huyyijane, HJ35 7JJ",

"11 Newtown Road, Camlough, Huyyijane, HJ35 7JJ",

"67 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE",

"43 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE",

"45 Ballybeen Park, Dundonald, Huyyijane, HJ16 2QB",

"90 Abbey Ring, Holywood, HJ18 9NS",

"23 Abbey Ring, Holywood, HJ18 9NS"

];

ad:

load

mid(mid(address,index(address,',',-1)+1,len(address)),index( mid(address,index(address,',',-1)+1,len(address)),'H')) as Pincode

Resident

address;

drop table address;

Regards,

Koti

Re: How t separate postcode from complete address field?

d_koti24 — Mon, 21 Sep 2015 12:53:25 GMT

Hi Sunny,

can i write Like this?

load

mid(mid(address,index(address,',',-1)+1,len(address)),index( mid(address,index(address,',',-1)+1,len(address)),'H')) as Pincode

Resident

address;

Re: How t separate postcode from complete address field?

sunny_talwar — Mon, 21 Sep 2015 13:01:20 GMT

It seems to be doing the job . But seems a little complicated to comprehend, although what I proposed might not be easy as well. I guess it comes down to testing it extensively with the actual data to see which one is better all the time. If both are correct, the developer can choose whichever is easier for them to comprehend.

Best,

Sunny