topic How to remove leading zeros in table box column? in QlikView

How to remove leading zeros in table box column?

Wed, 26 Nov 2014 11:20:21 GMT

Hi,

I have a phone number as below in table box.

0064524542444285

+2861244285

How can I change this to 64524542444285 and 2861244285.

can anyone suggest me please?

Thanks.

Re: How to remove leading zeros in table box column?

Wed, 26 Nov 2014 11:27:51 GMT

will purge char works?

Re: How to remove leading zeros in table box column?

sujeetsingh — Wed, 26 Nov 2014 11:35:10 GMT

subfield() function will do what you need.

Subfield(Your Field,'+',1) will give you number before +

Subfield(Your Field,'+',2) will give you number after +

Re: How to remove leading zeros in table box column?

Wed, 26 Nov 2014 11:37:43 GMT

Hi Amelia,

Purge would work for the + but not for the leading 0 (as you might have a zero later in the number).

Try something like

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

the purge removes the + and then converting to a num will remove the leading 0

hope that helps

Joe

Re: How to remove leading zeros in table box column?

Wed, 26 Nov 2014 11:39:48 GMT

Try this

Re: How to remove leading zeros in table box column?

Wed, 26 Nov 2014 11:42:13 GMT

Thanks,

What if the value is like below

00353 (0) 872686352

when I used Num(PurgeChar([Field1],'+')) it is showing blank where the value is 00353 (0) 872686352

please suggest me.

Re: How to remove leading zeros in table box column?

Wed, 26 Nov 2014 11:43:38 GMT

Expand the purge to include those extra chars

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

hope that helps

Re: How to remove leading zeros in table box column?

ashfaq_haseeb — Wed, 26 Nov 2014 11:48:53 GMT

Try this ad let me know if that worked.

Load *,

num(PurgeChar(Replace(Ltrim(replace(Value,0,' ')),' ',0),'+')) as New_Value;

load * Inline [

Value

0064524542444285

+2861244285

];

Regards

ASHFAQ

Re: How to remove leading zeros in table box column?

Wed, 26 Nov 2014 11:49:29 GMT

Thanks.

when I used this it is showing zeros where there are no zero.

actually the value is 87 7737377 but after using replace in the output it is showing 8707737377

Please suggest me how can I set this?

Re: How to remove leading zeros in table box column?

ashfaq_haseeb — Wed, 26 Nov 2014 11:51:12 GMT

Can you post few sample line of data.

Regards

ASHFAQ

Re: How to remove leading zeros in table box column?

Wed, 26 Nov 2014 11:54:17 GMT

Thanks.

01 0432482349

01 4973579

00353 (0) 87533453452

062 64444

044 343224540

087-7000208

059 9700021119

+872662083

I need to remove only first zero. I have to keep the zeros in middle and in last. Please suggest me how to set this?

Thanks.

Re: Re: How to remove leading zeros in table box column?

Wed, 26 Nov 2014 11:55:17 GMT

You Can Try this

Re: How to remove leading zeros in table box column?

Wed, 26 Nov 2014 11:56:53 GMT

Give that a try

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

Re: How to remove leading zeros in table box column?

ashfaq_haseeb — Wed, 26 Nov 2014 12:00:58 GMT

Try like this

Load *,

PurgeChar(Replace(Ltrim(replace(Value,0,'-')),'-',0),'+') as New_Value1,

replace(ltrim(replace(Value, '0', '-')), '-', 0) as New_Value;

load * Inline [

Value

01 0432482349

01 4973579

00353 (0) 87533453452

062 64444

044 343224540

087-7000208

059 9700021119

+872662083

];

Regards

ASHFAQ