Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Not applicable

Mid function along with index function

Hi Team,

I am trying to split phone number column into 3 parts base on a delimiter '-'.

I know that it's quite easy to resolve the above problem using subfield() function.

But, I want to try the same using Index function.

I have successfully achieved the leftmost and the rightmost column but i don't know what mistake i am doing in my logic to achieve the middle part of the phone number.

I have attached my qvw file for your reference.

Regards

Priyanka

1 Solution

Accepted Solutions

Re: Mid function along with index function

Try,

First-

=mid(Phone_Number.PhoneNumber,1,Index(Phone_Number.PhoneNumber,'-',1)-1)

Mid -

=mid(Phone_Number.PhoneNumber,Index(Phone_Number.PhoneNumber,'-',1)+1,Index(Phone_Number.PhoneNumber,'-',2)-(Index(Phone_Number.PhoneNumber,'-',1)+1))

Last-

=mid(Phone_Number.PhoneNumber,Index(Phone_Number.PhoneNumber,'-',2)+1)

View solution in original post

4 Replies
MVP
MVP

Re: Mid function along with index function

Try like

Left(PhoneNumber, Index(PhoneNumber,'-')-1) as lft,

Mid(PhoneNumber,Index(PhoneNumber,'-')+1,Index(PhoneNumber,'-',2)-Index(PhoneNumber,'-',1))as mid,

Index(PhoneNumber,'-')+1 as 1,

Len(PhoneNumber) as Len,

Index(PhoneNumber,'-',2) as 2,

Len(PhoneNumber)-Index(PhoneNumber,'-',2) as 3,

Right(PhoneNumber,Len(PhoneNumber)- Index(PhoneNumber,'-',2)) as right

Re: Mid function along with index function

Try,

First-

=mid(Phone_Number.PhoneNumber,1,Index(Phone_Number.PhoneNumber,'-',1)-1)

Mid -

=mid(Phone_Number.PhoneNumber,Index(Phone_Number.PhoneNumber,'-',1)+1,Index(Phone_Number.PhoneNumber,'-',2)-(Index(Phone_Number.PhoneNumber,'-',1)+1))

Last-

=mid(Phone_Number.PhoneNumber,Index(Phone_Number.PhoneNumber,'-',2)+1)

View solution in original post

antoniotiman
Honored Contributor III

Re: Mid function along with index function

Mid :

Mid([Phone-1.PhoneNumber],Index([Phone-1.PhoneNumber],'-')+1,Index([Phone-1.PhoneNumber],'-',2)-Index([Phone-1.PhoneNumber],'-')-1)

qlikviewwizard
Honored Contributor II

Re: Mid function along with index function

Hi Priyanka018,

Use this app.

Capture1.JPG

Capture.JPG