topic HOW TO: Remove leading zeros in QlikView

HOW TO: Remove leading zeros

disqr_rm — Wed, 24 Jun 2009 11:51:25 GMT

Found an interesting way to remove leading zeros from a field value. Thought I should share and see if you have a better way to achieve the same results.

Requirement was to remove leading zeros from field values of Material Numnber. This is a text field and can contain numeric or alpha-numeric values.

The trick I could do is:


Material:
load *, replace(ltrim(replace(OldMaterial, '0', ' ')), ' ', 0) as NewMaterial;
LOAD * INLINE [
OldMaterial
000001/230
0000012.34
0120-34.45
];

The result is:

OldMaterial	NewMaterial
0120-34.45	120-34.45
000001/230	1/230
0000012.34	12.34

Do you know a better way to perfor this conversion. Please remember, the field could contain a text value which may have leading zeros, which needs to be removed as well --> just num() doesn't work (even with isnum() function).

I got the results I was looking for but trying to find a better way. Otherwise, this post will serve others in future.

HOW TO: Remove leading zeros

disqr_rm — Wed, 24 Jun 2009 22:01:34 GMT

This is certainly a missing functionality (or function) in QV. I could recommend QT to enhance LTRIM function to be capable of taking a character in the parameter and remove that leading character occurences. Same can go with RTRIM and even for TRIM as well.

Somehting like: LTRIM(Text, '0')

HOW TO: Remove leading zeros

Thu, 10 Feb 2011 11:18:08 GMT

hello Rakesh.

thank you very much. You saved my lot of time. It was really helpful for me.

Best regards

Muncho

HOW TO: Remove leading zeros

Thu, 10 Feb 2011 11:26:50 GMT

Hi rakesh,

its looks good what you do.


Mid(AUFNR, FindOneOf(AUFNR, '123456789AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTyUuVvWwXxYyZz')) as Order_Key
[/n code]
yours seems to be a good one.
Thanks
Sravan</body>

HOW TO: Remove leading zeros

Anonymous — Mon, 21 Feb 2011 14:14:48 GMT

Rakesh,

How about this one:

evaluate(OldMaterial & '* 1')

HOW TO: Remove leading zeros

disqr_rm — Mon, 21 Feb 2011 14:29:09 GMT

Sure, if your material is numeric. Try with Material 000000000012345ABC.

But I surely liked the trick. Thank you.

HOW TO: Remove leading zeros

Tue, 02 Aug 2011 06:56:48 GMT

Hi Rakesh

Once again I have to thank you. I needed an answer to a seemingly simple question, remove leading zero’s. I found it here. Many thanks.

Regards Steve.

Re: HOW TO: Remove leading zeros

berndjaegle — Fri, 18 Dec 2015 07:29:43 GMT

Thanks Rakesh, seems to be an old question. Thanks for this post.