Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sparur
Specialist II
Specialist II

How I can remove leading zeroes

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

1 Solution

Accepted Solutions
sparur
Specialist II
Specialist II
Author

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

somebody has any ideas? 🙂

View solution in original post

31 Replies
sparur
Specialist II
Specialist II
Author

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

somebody has any ideas? 🙂

vupen
Partner - Creator
Partner - Creator

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.

skaredovs
Partner - Creator
Partner - Creator

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

sparur
Specialist II
Specialist II
Author

Hello Upendra.

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

sparur
Specialist II
Specialist II
Author

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?

skaredovs
Partner - Creator
Partner - Creator

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



vupen
Partner - Creator
Partner - Creator

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.

sparur
Specialist II
Specialist II
Author

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.

guytzumer
Partner - Contributor III
Partner - Contributor III

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,