topic Re: How do I remove preceeding zero's from Numbers in QlikView

How do I remove preceeding zero's from Numbers

Thu, 27 Sep 2012 16:08:51 GMT

Hello, in the data base I work with I have a mix of numbers where some have preceeding zeros and some dont for example.

0000001311551 >>1311551

0000000454545 >>454545

1254550000101>>1254550000101

0000000045454>>45454

The numbers after the >> are the way I want to see the numbers in my report, How do I go about this?

Thanks.

Paul.

Re: How do I remove preceeding zero's from Numbers

Thu, 27 Sep 2012 16:22:57 GMT

Try this:

replace(ltrim(replace(MyField,'0',' ')),' ','0') AS MyField

This code first uses the replace() function to replace all zeros in the field with a space and then uses the ltrim() function which removes spacing from the begining of a string only. We then use replace() again to convert any remaining spaces back to zeros.

Source: http://www.qlikviewaddict.com/2012/03/removing-leading-zeros.html

Re: How do I remove preceeding zero's from Numbers

rwunderlich — Thu, 27 Sep 2012 22:10:50 GMT

Another solution that I find easier to read is adding a zero.

MyField+0 as MyField

-Rob