topic Re: Using INDEX function in QlikView

Using INDEX function

Nagaraju_KCS — Tue, 03 Sep 2013 04:55:53 GMT

Hi All,

Phone:

Load * inline[

Phone

98859843841];

i want to return 988-584-3841 this format

Phone:

LOAD

Phone,

Left(Phone, Index(Phone, '-')-1) as Phone1,

Mid(Phone, Index(Phone, '-')+1, 3) as Phone2,

Right(Phone, Len(Phone)-Index(Phone, '-', 2)) as Phone3

Resident Phone;

Re: Using INDEX function

jagan — Tue, 03 Sep 2013 05:13:21 GMT

Hi,

Try like this

=Left('98859843841', 3) &'-'&Mid('98859843841', 4,3) &'-'&Right('98859843841', 4)

will gives u this format 988-584-3841

If you want 98859843841 from 988-584-3841

Then use

SubField()

SubField('988-584-3841', '-') will gives you 988

SubField('988-584-3841', '-', 2) will gives you 584

SubField('988-584-3841', '-', 3) will gives you 3841

Regards,

Jagan.

Re: Using INDEX function

Tue, 03 Sep 2013 05:16:36 GMT

Hola Raju,

Do you want 9885843841 to be 988-584-3841? Or, do you want 988-584-3841 to be 9885843841?

Why don't you concatenate the string Phone1, Phone2 and Phone3?

Regards, Karl

Re: Using INDEX function

tresB — Tue, 03 Sep 2013 05:17:10 GMT

Try:

Phone:

LOAD

Phone,

Left(Phone, 3) as Phone1,

Mid(Phone, 4,3) as Phone2,

Mid(Phone,8) as Phone3,

Left(Phone, 3) &'-'& Mid(Phone, 4,3)&'-'& Mid(Phone, 😎 as PhoneWithSeparator

Resident Phone;

Re: Using INDEX function

Tue, 03 Sep 2013 05:19:47 GMT

try this

there are many ways, but try one,

=Left('98859843841',3) &'-' &left(mid('98859843841',4,4),1)&right(mid('98859843841',4,4),2) &'-'& Right('98859843841',4)

or try this

=Left(PHONE,3) &'-' &left(mid(PHONE,4,4),1)&right(mid(PHONE,4,4),2) &'-'& Right(PHONE,4)

Re: Using INDEX function

Tue, 03 Sep 2013 07:11:49 GMT

This worked...

PhoneNumber:

LOAD * INLINE [
Phone
98859843841
];

Try this..
Phone:
LOAD
Phone,
left(Phone,3)&'-'& mid(Phone,4,3)&'-'&Right(Phone,4) as new
Resident PhoneNumber;

Re: Using INDEX function

sunilkumarqv — Fri, 08 May 2015 05:58:54 GMT

try this in your script

LOAD Left(Phone, 3) &'-'&Mid(Phone, 4,3) &'-'&Right(Phone, 4) as Phone;

Load * inline

[

Phone

98859843841];