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

[dileepchalasani]

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.

[sunny_talwar · Accepted solution]

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]

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]

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]

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-001	05241X-001
05491X-001	05491X-001
00000000000100345	100345
00000000000100692	100692

[dileepchalasani]

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]

Stefan,

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