topic How I can remove leading zeroes in QlikView

How I can remove leading zeroes

sparur — Wed, 14 Apr 2010 08:37:44 GMT

Hello, colleagues.I have a small problem, which I can't resolve elegant.

I want to remove leading zeroes from my Data.

I tried to do some text / num converting, but it didn't help me.

See attached file

How I can remove leading zeroes

sparur — Wed, 14 Apr 2010 08:51:39 GMT

I resolved my problem, but I do not like my solution. I believe that this problem can resolve more elegant.

somebody has any ideas? 🙂

How I can remove leading zeroes

vupen — Wed, 14 Apr 2010 09:02:23 GMT

Apparently, your column MATNR has string characters (alphabets). So its not a number in string format. This is why the num() is returning a NULL. You should first make MATNR a number only column, then apply a num() function.

How I can remove leading zeroes

skaredovs — Wed, 14 Apr 2010 09:03:12 GMT

Hi Anatoly,

I've tried different things, but seams like you solved it in almost shortest way. You can little simplify this code by using UPPER() and APPLYCODEPAGE() to get free of special letters.

Best regards,

Konstantins

How I can remove leading zeroes

sparur — Wed, 14 Apr 2010 09:11:29 GMT

Hello Upendra.

Thank for your reply, but I know why function num() is returning null value. I tried different converting variants 🙂

How I can remove leading zeroes

sparur — Wed, 14 Apr 2010 09:13:47 GMT

Hi, Konstantins.

Thank you for your idea with UPPER() function. I will apply it. but I don't understand how I can apply second function (applycodepage). How it will help me?

How I can remove leading zeroes

skaredovs — Wed, 14 Apr 2010 09:37:45 GMT

Hi, Anatoly.

I thought to use applycodepage() to get free of special characters, but it works not the way I want.

So, if you don't have special characters, then no problem.

Good luck,

Konstantins

How I can remove leading zeroes

vupen — Wed, 14 Apr 2010 09:51:55 GMT

May be this is what you are looking for. I have written a Macro function which takes the column value as the paramter and trims all the leading zeros.

How I can remove leading zeroes

sparur — Wed, 14 Apr 2010 10:12:35 GMT

Upendra, Thank you for your suggestion, but I don't like to use a macro. I use it only when I can't resolve problem without it. But your approach is solution also. Thank.

How I can remove leading zeroes

guytzumer — Thu, 15 Apr 2010 08:34:01 GMT

MATNR - you mean SAP I guess..?

if so.. the Characters that define MATNR (in the R3 system) is MIX with Numbers & Text

one solution is

TEXT(IF(INDEX(TEXT(Material_MATNR),'0000')=1,

NUM(RIGHT(TEXT(Material_MATNR),LEN(TEXT(Material_MATNR))-4)),TEXT(Material_MATNR))) as Material,

How I can remove leading zeroes

sparur — Thu, 15 Apr 2010 10:07:15 GMT

Hello,

Yes I Load data from SAP, but not R3, I Use SAP BW.

Ufortunally, I can't use your approach because I don't Know how much zeroes are leading in each Material.

How I can remove leading zeroes

Thu, 15 Apr 2010 10:58:45 GMT

Take a look at the enclosed where leading zeros are removed.

How I can remove leading zeroes

sparur — Thu, 15 Apr 2010 11:37:28 GMT

Hello, DK_ASN

That's a great. Thank you for your suggestion.

How I can remove leading zeroes

Mon, 09 May 2011 23:25:55 GMT

I have had great success with

evaluate(OriginalField & '* 1') as MyStrippedField;

Evaluate to force the field to be evaluated as a numeric the & '* 1' portion makes that evaluation against algebraic identity. if I need to also format a decimal point such as a string with leading zeros that also needs a decimal point placed as money

evaluate(OriginalField & '* .01') as MyStrippedField;

Or whatever the number of characters you need the field moved .1 .01 .001 etc.

Hope this helps.

Nick Dighera

WIT Inc Troy Michigan

How I can remove leading zeroes

disqr_rm — Mon, 09 May 2011 23:44:47 GMT

This always worked great for me:

replace(ltrim(replace(MATNR, '0', ' ')), ' ', 0)

How I can remove leading zeroes

lalitinmbd — Tue, 10 May 2011 05:04:13 GMT

You can use the following code

may be this can help you out.

mid(MATNR,FindOneOf(MATNR,'123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'))

Re: How I can remove leading zeroes

Fri, 15 Jun 2012 13:42:36 GMT

Thanks Rakesh

Very elegant

Re: How I can remove leading zeroes

lkartchner — Tue, 01 Apr 2014 02:09:47 GMT

This worked great! Thank you.

Re: How I can remove leading zeroes

Wed, 03 Dec 2014 17:57:07 GMT

Great! far more simple than what I've been using up to now!

Thank you!

Re: How I can remove leading zeroes

Mon, 02 Feb 2015 16:57:54 GMT

Than you very much.