topic substring formula in QlikView

substring formula

Mon, 03 Aug 2009 16:17:13 GMT

Hi Team

From Address field, i have to extract the pincode written in it. But there is no specific position where it can start in the Address field.

What my idea is start from position 1 till the end of the Address value, take 6 char everytime and check whether it is Num.

i am writing VB macro for it.

Is there any QV function to do so?

Also pls suggest if there is any other way to do the same.

Its urgent.

Thanks.

Arun Mittal

substring formula

Mon, 03 Aug 2009 16:44:35 GMT

Hi Arun,

Sql equilvalent substring function in qv is mid

for e.g string

address1 = "Mumbai - 400092"

mid(address1,1,6) will return '400092'

mid(address2,10,6) will return '400092'

You can use the left function also if the PIN number starts from position 1 .

for e.g.

left(address1,6) will also return '400092'

Best regards,

tapas

substring formula

Mon, 03 Aug 2009 16:49:21 GMT

Hi,

Can you post the sample of your data.?

- Sridhar

substring formula

Mon, 03 Aug 2009 16:50:23 GMT

Thank for the reply tapas.

But, as i clearly mentioned in the post that position for Pincode value is not fixed in my Address Field.

substring formula

Mon, 03 Aug 2009 16:52:56 GMT

HDFC Bank Ltd., Kolkatta - 700 001, WB

HDFC Bank Ltd., 700 001 Kolkatta WB

HDFC Bank Ltd., Kolkatta, WB - 700 001

As u can see, in above three records, position of pincode is different.

substring formula

Mon, 03 Aug 2009 17:01:54 GMT

Hi,

check this attached application.

- Sridhar

substring formula

Mon, 03 Aug 2009 17:12:36 GMT

Thanks a lot for the reply. But what happened if the data is like below:

1st Floor, K.B.Road, Near National Insurance Co, Jorhat-785001

2nd Floor, President Tower, Thana Road, Jorhat, Assam - 785001

Deepali Medical Complex, 2 nd floor, A.T.Road, Jorhat - 785001

Ground Floor,Shop No1, Laxmi Narayan Avenue, G.T.Road,Murgasol,Asansol,Dist- Burdwan,West Bengal-713303.

if string contains some other numeric chars also, result will be concatenation of all those like for first record it will be - 1785001...

substring formula

Mon, 03 Aug 2009 19:04:50 GMT

Hi Amit,

check this attached application.

- Sridhar

substring formula

Mon, 03 Aug 2009 20:39:18 GMT

Thank you very much.

Let me check whether this will be applicable for my scenerio. Will catch u if any other required

🙂