Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Another solution that I find easier to read is adding a zero.
MyField+0 as MyField
-Rob