topic Re: How to remove space and bracket in numbers? in QlikView

How to remove space and bracket in numbers?

Wed, 26 Nov 2014 17:15:40 GMT

Hi,

I have phone number as below

00353 (0) 87 1706453

I can remove leading zeroes by using below function.

Replace(Replace(Ltrim(replace(replace(replace(Number,' ','_'),0,' '),'+',' ')),' ',0),'_',' ') as Number_1

can anyone help me how to remove spaces and brackets

how to replace 4765 in place of 0 which is in first place for below number

0504634545

Thanks.

Re: How to remove space and bracket in numbers?

Anonymous — Wed, 26 Nov 2014 17:17:31 GMT

Have a look at the PurgeChar() function.

Re: How to remove space and bracket in numbers?

stevedark — Wed, 26 Nov 2014 17:23:33 GMT

Or better still, the keepchar function:

KeepChar(Number, '0123456789')

You can also add in other characters, such as period and comma, if you want to keep those also.

Steve

Re: How to remove space and bracket in numbers?

Wed, 26 Nov 2014 17:29:44 GMT

Num(Trim(PurgeChar([Field1],'-+()')))

will turn

00353 (0) 87 1706453

into

3530871706453

which is a believe what you are ultimately after?

hope that helps

Joe

Re: How to remove space and bracket in numbers?

shree909 — Wed, 26 Nov 2014 17:30:56 GMT

Use which ever works for you

=Mid(KeepChar(Number, '0123456789'),3) or if you are getting 00 at the begining use and if condition

=If(Mid(KeepChar(Number, '0123456789'),1,2)='00',Mid(KeepChar(Number, '0123456789'),3),KeepChar(Number, '0123456789')) AS Number_1

Re: How to remove space and bracket in numbers?

kushalthakral — Wed, 26 Nov 2014 17:48:05 GMT

Hi Amelia

Please use below formual, it will fulfill you requirement

num(Mid(KeepChar(Number, '0123456789'),3))

please check below image

Re: How to remove space and bracket in numbers?

MarcoWedel — Wed, 26 Nov 2014 21:33:37 GMT

Num(KeepChar(Number, '0123456789'))

Re: Re: How to remove space and bracket in numbers?

MarcoWedel — Wed, 26 Nov 2014 23:44:14 GMT

='4765'&Replace(LTrim(Replace(KeepChar('00353 (0) 87 1706453', '0123456789'),'0',' ')),' ','0')

hope this helps

regards

Marco

Re: Re: How to remove space and bracket in numbers?

MarcoWedel — Wed, 26 Nov 2014 23:46:49 GMT

The "Num(KeepChar(Number, '0123456789'))" approach I posted before does not work for numbers as long as your example because it can't be intertreted as an integer (to long).

hope this helps

regards

Marco

Re: How to remove space and bracket in numbers?

Gabriel — Wed, 26 Nov 2014 23:59:49 GMT

Hi Amelia

I suggest may be try this

=4765 & Mid(KeepChar('00353 (0) 87 1706453','0123456789'),3)

Hope it helps

Gabriel

Re: How to remove space and bracket in numbers?

Thu, 27 Nov 2014 10:04:13 GMT

Thanks.

How to use column name in same formula to replace all column values? please suggest me

Re: How to remove space and bracket in numbers?

MarcoWedel — Thu, 27 Nov 2014 10:13:02 GMT

='4765'&Replace(LTrim(Replace(KeepChar(FieldName, '0123456789'),'0',' ')),' ','0')

Re: How to remove space and bracket in numbers?

Thu, 27 Nov 2014 10:23:23 GMT

Thanks very much.

there are some n/a, N/A , none values in column data. when I applied the formula it is showing 4765 in place of n/a, N/A , none. How to set this so that in place of n/a, N/A , none how to show blanks

input	output
n/a	4765
N/A	4765
none	4765
none	4765

Please suggest.

Re: How to remove space and bracket in numbers?

stevedark — Thu, 27 Nov 2014 20:09:35 GMT

Take the expression you have and place it in an IF statement, checking N as the first character:

=if(lower(left(input, 1) = 'n', '', '4765'&Replace(LTrim(Replace(KeepChar(input, '0123456789'),'0',' ')),' ','0'))

Steve

Re: Re: How to remove space and bracket in numbers?

MarcoWedel — Thu, 27 Nov 2014 21:39:07 GMT

Hi,

one solution could be:

tabPhoneNo:
LOAD If(not WildMatch(phoneNo, 'n/a','none'),Text('4765'&Replace(LTrim(Replace(KeepChar(phoneNo, '0123456789'),'0',' ')),' ','0'))) as phoneNo,
    RecNo() as ID 
INLINE [
    phoneNo
    NONE
    00353 (0) 87 1706453
    None
    003530871706452
    n/a
    ++353 (0) 87 1706451
    N/A
    +353 (0) 87 1706450
    087-1706450
    none
];

hope this helps

regards

Marco

Re: How to remove space and bracket in numbers?

agomes1971 — Fri, 26 Aug 2016 11:05:31 GMT

Hi,

is this issue solved?

If not here it goes my suggestion:

To remove spaces

Replace('00353 (0) 87 1706453',' ','')

To replace 0 to 4765

replace('0504634545','0','4765')

Please mark the correct answer to close this issue.

HTH

André Gomes