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: 
dileepchalasani
Contributor II
Contributor II

How to Trim Leading Zero's from Numeric value of a Alpha Numeric datatype

Hello All,

Need Help in Trimming Leading zero's from a Alpha Numeric data type.

We bring data to Qlik Sense from SAP BW. In this Case Material which is a 18 digit alphanumeric character.

My Data looks like:

00000000000100345

00000000000100692

05241X-001

05491X-001

I would like to remove leading zeros from only numeric fields. The result set should be like

100345

100692

05241X-001

05491X-001

I am currently using the below script for Customer data to remove leading zero's which is working, this will not work for Material because it will trim Zero's for 05241X-001 value also.

Replace(LTrim(Replace(CUSTOMER,'0',' ')),' ','0') as Customer

I searched other Qlik Threads for solution also but didn't find anything helpful.

Any Suggestions will be appreciated.

Thanks

Dileep Chalasani.

1 Solution

Accepted Solutions
sunny_talwar

May be this

If(IsNum(Replace(LTrim(Replace(CUSTOMER,'0',' ')),' ','0')), Replace(LTrim(Replace(CUSTOMER,'0',' ')),' ','0'), CUSTOMER) as Customer

View solution in original post

8 Replies
sunny_talwar

May be this

If(IsNum(Replace(LTrim(Replace(CUSTOMER,'0',' ')),' ','0')), Replace(LTrim(Replace(CUSTOMER,'0',' ')),' ','0'), CUSTOMER) as Customer

sunny_talwar

Or this

Alt(Num(Replace(LTrim(Replace(CUSTOMER,'0',' ')),' ','0')), CUSTOMER) as Customer

chriscammers
Partner - Specialist
Partner - Specialist

I think this would work

Text(If(isnum(foo),Num(foo),foo))

This one returns a text value if you want the field to be mixed with text and numbers then pull off the Text() function but then in list boxes you will see the strings lined up to the left and numbers lined up to the right.

chriscammers
Partner - Specialist
Partner - Specialist

Never mind, I did not read the original post very carefully.

is the pattern with the dash consistent? for example you could use subfield

Text(If(isnum(subfield(foo,'-',1)),Num(subfield(foo,'-',1)),subfield(foo,'-',1))) & If(Len(subfield(foo,'-',2)>0,'-' & Text(If(isnum(subfield(foo,'-',2)),Num(subfield(foo,'-',2)),subfield(foo,'-',2)))


Of course if pattern with the dashes is not consistent or if you have values with more than one dash then you need to figure out what the most number of dashes is and write a dynamic expression based on that.


ugh that sounds terrible.

swuehl
MVP
MVP

If your 18 digit alphanumeric material actually might show only up to 14 significant numeric digits...

Slightly changing Chris initial approach:

LOAD *,

     Text(If(Isnum(evaluate(Test)),Num(evaluate(Test)),Test)) as Test2

INLINE [

Test

00000000000100345

00000000000100692

05241X-001

05491X-001

];

Test Test2
05241X-00105241X-001
05491X-00105491X-001
00000000000100345100345
00000000000100692100692
dileepchalasani
Contributor II
Contributor II
Author

Thanks Sunny, This is working, i am able to trim zero's for only numeric fields.

sunny_talwar

Awesome

Please close the thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

dileepchalasani
Contributor II
Contributor II
Author

Stefan,

This is working for most of the cases except where the numeric field has dash, like 122-01-01.