topic Re: Extracting Alphanumeric content from a string in QlikView

Extracting Alphanumeric content from a string

QlikView_99 — Mon, 09 Sep 2019 14:09:58 GMT

Hi All,

I have a address column where, it is mixed up with street, region, city, pincode. The data is not consistent in hierarchy of the address, sometimes the house number is mentioned and some cases only city and pincode are mentioned.

Now I need to extract only the pincode from this data, below is the type of data i have in the column "Address" and required format of output is in column "Required Pincode". Request you all to help me in sorting this situation.

Address	Required Pincode
London, W4 1NJ	W4
Burney Avenue, Surbiton, EC4A 3HQ	EC4A
Stanley Hill, Amersham, HP7 9ET	HP7

Re: Extracting Alphanumeric content from a string

sunny_talwar — Mon, 09 Sep 2019 14:25:54 GMT

One option is this

~~TextBetween(Address, ', ', ' ')~~

TextBetween(', ' & SubField(Address, ', ', -1), ', ', ' ') as [Pincode]

Re: Extracting Alphanumeric content from a string

StarinieriG — Mon, 09 Sep 2019 14:24:18 GMT

Hi,

try in this way:

Subfield(Trim(Subfield(Trim(Address),',',-1)),chr(32),1)

Re: Extracting Alphanumeric content from a string

QlikView_99 — Mon, 09 Sep 2019 14:48:16 GMT

Hello,

I used the below line of code to reflect the same.

textbetween(SubField(Address,',',-1),' ',' ') as Pincode

Re: Extracting Alphanumeric content from a string

QlikView_99 — Mon, 09 Sep 2019 14:56:22 GMT

But i do have one more challenge in extracting the pincode, where there is no pincode at all and there is some location in that, so i need to tag that location's pincode to the address column, added is the description in "Address" column for an airport.

Address	Required Pincode
London, W4 1NJ	W4
Burney Avenue, Surbiton, EC4A 3HQ	EC4A
Stanley Hill, Amersham, HP7 9ET	HP7
Manchester Airport (upon research in internet pincode is M90)	M90

Re: Extracting Alphanumeric content from a string

sunny_talwar — Mon, 09 Sep 2019 15:19:33 GMT

But if it is not available, how can we magically create a value... You will need to bring this information from somewhere somehow