topic Re: Phone numbers clean up in QlikView

Phone numbers clean up

snehamahaveer — Wed, 25 Nov 2020 16:16:04 GMT

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,

Anil_Babu_Samineni — Mon, 14 Aug 2017 16:32:38 GMT

May be this?

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

el_aprendiz111 — Mon, 14 Aug 2017 16:35:25 GMT

Hi,

=SubField(120888888.00 ,'.',1)

=SubField(MyField ,'.',1)

antoniotiman — Mon, 14 Aug 2017 16:38:10 GMT

Maybe this

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

snehamahaveer — Mon, 14 Aug 2017 16:42:06 GMT

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 — Mon, 14 Aug 2017 16:44:39 GMT

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,

antoniotiman — Mon, 14 Aug 2017 16:58:39 GMT

Can You provide an example ?

sasiparupudi1 — Tue, 15 Aug 2017 07:40:24 GMT

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