Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
snehamahaveer
Contributor

Phone numbers clean up

Hi All,

I am currently working on cleaning up customer data on our database.

The issue is some phone numbers have '.00' at the end and I am finding it difficult to remove them.

The case to resolve is... if a phone number has '.00' - eg 120888888.00 then it should be converted to 0120888888.

My formula is given below:

=if(Right(Text([Phone Number]),3)='.00',

0&Left(Text([Phone Number]),Len(Text([Phone Number]))-3),

Text([Phone Number]))

Can someone help with this. My script is finding it difficult to differentiate between '.00' and '000'.

Many thanks,

SM

1 Solution

Accepted Solutions
sasiparupudi1
Honored Contributor III

Re: Phone numbers clean up

if(index([Phone Number],'.')>0,0 &SubField([Phone Number], '.',1),[Phone Number])

7 Replies

Re: Phone numbers clean up

May be this?

=If(Right('120888888.00',2)='00', '0' & SubField('120888888.00', '.', 1))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
el_aprendiz111
Valued Contributor

Re: Phone numbers clean up

Hi,

=SubField(120888888.00 ,'.',1)

=SubField(MyField ,'.',1)

antoniotiman
Honored Contributor III

Re: Phone numbers clean up

Maybe this

=Num(SubField('120888888.00','.',1),'0000000000')

snehamahaveer
Contributor

Re: Phone numbers clean up

Hi Anil,

Thank you for the prompt response.

Your formula works well when there is .00 involved. However when it is not the case  eg phone number = 0288288888 then it changes it to 00288288888.

Is there a way to formulate this on both scenarios?

snehamahaveer
Contributor

Re: Phone numbers clean up

Hi Antonio,

I guess your formula works for 99% of my customers. However, is there a way to keep the spaces between the phones if any are found.?

Thanks,

SM

antoniotiman
Honored Contributor III

Re: Phone numbers clean up

Can You provide an example ?

sasiparupudi1
Honored Contributor III

Re: Phone numbers clean up

if(index([Phone Number],'.')>0,0 &SubField([Phone Number], '.',1),[Phone Number])