topic How to remove the leading zero's in the alphanumeric field values in QlikView

How to remove the leading zero's in the alphanumeric field values

Wed, 29 Jul 2009 01:17:49 GMT

The data looks like below one of my reporting field

2412HJk0067
0161R11U2
0000000000000002326
0000000000000005338
973-GV-79
0000000000000008349
834-PV-62
0164DU636
00000000000000078517
1437/392
##2WQR4567
-0089WSE9
-08213R8756
-00006543298

Database the field has data type character and values are not maintained the same length .

Data extraction it is extracted as

text(field1) as field1

I would like to remove leading 0 atleast for the values like 0000000000000007851 to 7851.

I tried Num,num#,trim ,left- nothing is working.

Advance thanks for your help.

How to remove the leading zero's in the alphanumeric field values

jbeierschmitt — Wed, 29 Jul 2009 02:07:31 GMT


mid([GLPCA.RACCT],index([GLPCA.RACCT],left(PurgeChar([GLPCA.RACCT] , '0' ),1)))
 [ /code ]
I use this as my standard for mixed numbers and to remove leading zeros

Jim</body>

How to remove the leading zero's in the alphanumeric field values

disqr_rm — Wed, 29 Jul 2009 02:16:31 GMT

Look here:

http://community.qlik.com/forums/t/17456.aspx

How to remove the leading zero's in the alphanumeric field values

jbeierschmitt — Wed, 29 Jul 2009 02:22:16 GMT

I use this as a standard with no problems:

mid([GLPCA.RACCT],index([GLPCA.RACCT],left(PurgeChar([GLPCA.RACCT] , '0' ),1)))

How to remove the leading zero's in the alphanumeric field values

Wed, 29 Jul 2009 02:25:53 GMT

Thanks for your solution .

It is working

How to remove the leading zero's in the alphanumeric field values

prieper — Wed, 29 Jul 2009 02:33:33 GMT

Hi,

seems to me like a bug. Have experimented and apparently QV interprets numbers with more than

14 leading "0"s always as a text, with lesser "0"s it would interpret a number like 0000000000000078517 as 78517.

You might use the Replace-function:

Replace(YourField, '00000000000000', '') AS TextOrNumber

but this will fail for e.g. 00000000000000078517A as it would display then 78517A and no longer the full string, which is probably needed.

HTH

Peter

How to remove the leading zero's in the alphanumeric field values

disqr_rm — Wed, 29 Jul 2009 02:40:21 GMT

This is why I recommend using replace with trim combination. Otherwise you will have problem with "long" numbers.

replace(ltrim(replace(OldMaterial, '0', ' ')), ' ', 0) as NewMaterial

How to remove the leading zero's in the alphanumeric field values

prieper — Wed, 29 Jul 2009 03:43:40 GMT

Hi Rakesh,

our messages crossed - pretty nice solution. Though depending on the meaning of the data it might be of importance whether you have 0000456A or 456A ......

Peter

Re: How to remove the leading zero's in the alphanumeric field values

tmumaw — Wed, 05 Dec 2012 13:30:44 GMT

Rakesh,

Thanks for the example. It works great.

Thom