Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
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
Highlighted
Honored Contributor III

Re: Phone numbers clean up

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

View solution in original post

7 Replies
Highlighted

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)
Highlighted
Valued Contributor

Re: Phone numbers clean up

Hi,

=SubField(120888888.00 ,'.',1)

=SubField(MyField ,'.',1)

Highlighted
Honored Contributor III

Re: Phone numbers clean up

Maybe this

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

Highlighted
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?

Highlighted
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

Highlighted
Honored Contributor III

Re: Phone numbers clean up

Can You provide an example ?

Highlighted
Honored Contributor III

Re: Phone numbers clean up

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

View solution in original post