Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

1 Solution

Accepted Solutions
jbeierschmitt
Contributor III
Contributor III


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>

View solution in original post

8 Replies
jbeierschmitt
Contributor III
Contributor III


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>

View solution in original post

disqr_rm
Partner
Partner

jbeierschmitt
Contributor III
Contributor III

I use this as a standard with no problems:

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

Not applicable
Author

Thanks for your solution .

It is working Yes

prieper
Master II
Master II

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

disqr_rm
Partner
Partner

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

prieper
Master II
Master II

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

tmumaw
Specialist II
Specialist II

Rakesh,

Thanks for the example.  It works great.

Thom