topic Re: Remove leading zeroes in QlikView

Remove leading zeroes

Mon, 21 Dec 2015 15:56:52 GMT

Hi,

I wanted to remove leading zeroes from the filed loading from text file(Notepad). But it has negative numbers as well.

The following function is working fine only for positive numbers but not negative numbers.

Please suggest, if Anybody has worked with negative numbers before,.

REPLACE(num(LTRIM(REPLACE(VARIANCE, '0',' '))),' ', '0') ==> WORKING FOR POSITIVE NUMBER ONLY

VARIANCE

-000000000000219.34
000000000000219.34
000000000000219.34
-000000000000219.34
000000000000700.00
-000000000000700.00
-000000000000700.00
000000000000700.00
000000000000200.00
-000000000000200.00
-000000000000200.00
000000000000200.00
-000000000000480.66
000000000000480.66
000000000000480.66
-000000000000480.66
-000000000000361.38
000000000000361.38
000000000000361.38
-000000000000361.38
-000000000000000.30
000000000000000.30
-000000000000000.07
000000000000000.07
-000000000000000.05
000000000000000.05
000000000053407.96
-000000000053407.96

-000000000000004.72

Re: Remove leading zeroes

Peter_Cammaert — Mon, 21 Dec 2015 16:04:22 GMT

Try this (QV accepts only numbers that are max 14 digits long)

num#(mid(VARIANCE, FindOneOf(VARIANCE, '123456789.'))) * IF (Left(VARIANCE,1) = '-', (-1), 1)

Best,

Peter

Re: Remove leading zeroes

sunny_talwar — Mon, 21 Dec 2015 16:07:18 GMT

May be this:

Table:

LOAD VARIANCE,

Num(Num#(Left(Trim(VARIANCE), 1) & Right(Trim(VARIANCE), 9), '###0.00;-###0.00'), '#,##0.00;-#,##0.00') as NewVARIANCE;

LOAD * Inline [

VARIANCE

-000000000000219.34

000000000000219.34

-000000000000219.34

000000000000700.00

-000000000000700.00

000000000000700.00

000000000000200.00

-000000000000200.00

000000000000200.00

-000000000000480.66

000000000000480.66

-000000000000480.66

-000000000000361.38

000000000000361.38

-000000000000361.38

-000000000000000.30

000000000000000.30

-000000000000000.07

000000000000000.07

-000000000000000.05

000000000000000.05

000000000053407.96

-000000000053407.96

-000000000000004.72

];

Re: Remove leading zeroes

settu_periasamy — Mon, 21 Dec 2015 16:23:41 GMT

Maybe try with fabs

REPLACE(num(LTRIM(REPLACE(fabs (VARIANCE), '0',' '))),' ', '0')

Re: Remove leading zeroes

Mon, 21 Dec 2015 16:37:38 GMT

Hi,

i thing this is the code which you looking for

=If(Left(Variance,1)='-',-1 * Num(Trim(Replace(mid(Variance,2),'0',''))) ,Num(Trim(REPLACE(Variance, '0',' '))))

thanks,

Pratyush Nautiyal

Re: Remove leading zeroes

MarcoWedel — Mon, 21 Dec 2015 23:23:11 GMT

Hi,

other solutions could be (with or without keeping your two fixed decimal places):

table1:

LOAD *,

Num(Evaluate(VARIANCE)) as VARIANCE2,

Num(Evaluate(VARIANCE),'#0.00') as VARIANCE3

Inline [

VARIANCE

-000000000000219.34

000000000000219.34

-000000000000219.34

000000000000700.00

-000000000000700.00

000000000000700.00

000000000000200.00

-000000000000200.00

000000000000200.00

-000000000000480.66

000000000000480.66

-000000000000480.66

-000000000000361.38

000000000000361.38

-000000000000361.38

-000000000000000.30

000000000000000.30

-000000000000000.07

000000000000000.07

-000000000000000.05

000000000000000.05

000000000053407.96

-000000000053407.96

-000000000000004.72

];

hope this helps

regards

Marco