topic Re: How To Remove Leading Zeros in a Number in QlikView

How To Remove Leading Zeros in a Number

Fri, 22 Aug 2014 13:24:42 GMT

Hi All,

I have a field with the following values

00000978

00001524

00001024

00012356

The field is in number format.

The length of the field is constant and it is 8.

I would like to remove the leading zeros and would like to have the values as follows:

978

1524

1024

12356

Kindly help me to get the above output.

Thanks in Advance!!

Regards,

Vinitha.

Re: How To Remove Leading Zeros in a Number

MayilVahanan — Fri, 22 Aug 2014 13:29:05 GMT

Try like this

Num(Num#(FieldName,'#,##0'),'#,##0') AS FieldName;

Num(FieldName)

Re: How To Remove Leading Zeros in a Number

nagaiank — Fri, 22 Aug 2014 13:31:32 GMT

Use the script below

LOAD *, Number8digit+0 As Number;
LOAD * Inline [
Number8digit
00000978
00001524
00001024
00012356
];

Re: How To Remove Leading Zeros in a Number

MK_QSL — Fri, 22 Aug 2014 13:37:21 GMT

Load

NUMBER,

Num(NUMBER) as NUMBER1,

NUM(Right(NUMBER, Len(NUMBER)-FindOneOf(NUMBER, '123456789')+1)) as NUMBER2

Inline

[

NUMBER

00000978

00001524

00001024

00012356

];

Re: How To Remove Leading Zeros in a Number

ashfaq_haseeb — Sun, 24 Aug 2014 09:53:39 GMT

Hi,

You can try below code.

LOAD *,

replace(ltrim(replace(Number, '0', ' ')), ' ', 0) as New_Number;

LOAD * Inline [

Number

00000978

00001524

00001024

00012356

];

Regards

ASHFAQ

Re: How To Remove Leading Zeros in a Number

MarcoWedel — Sun, 24 Aug 2014 10:24:06 GMT

Num(YourField)

should be the easiest way.

Re: How To Remove Leading Zeros in a Number

Mon, 25 Aug 2014 05:27:22 GMT

Hi Vinitha

you can perform below code in your existing work space. try to run below and check.

Load

NUMBER,

Num(NUMBER) as NUMBER1,

NUM(Right(NUMBER, Len(NUMBER)-FindOneOf(NUMBER, '123456789')+1)) as NUMBER2

Inline

[

NUMBER

00000978

00001524

00001024

00012356

];

Re: How To Remove Leading Zeros in a Number

fabioporta — Thu, 07 Dec 2017 10:15:29 GMT

Mid(FieldValue, FindOneOf(FieldValue,'123546789'))

Re: How To Remove Leading Zeros in a Number

Femke — Fri, 06 Dec 2019 13:58:58 GMT

this worked for me:

Num(Num#(yourfield,'#,##0'),'#0') AS FieldName

Re: How To Remove Leading Zeros in a Number

suepenick — Thu, 16 Jan 2020 20:27:24 GMT

this one worked perfectly

Re: How To Remove Leading Zeros in a Number

JeromeNagarro — Thu, 01 Sep 2022 08:42:49 GMT

Text(Alt(FieldName + 0, FieldName)) as FieldName