topic Re: How to Trim Leading Zero's from Numeric value of a Alpha Numeric datatype in App Development

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

dileepchalasani — Tue, 22 Aug 2017 19:09:26 GMT

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.

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

sunny_talwar — Tue, 22 Aug 2017 19:33:48 GMT

May be this

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

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

sunny_talwar — Tue, 22 Aug 2017 19:34:55 GMT

Or this

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

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

chriscammers — Tue, 22 Aug 2017 20:51:32 GMT

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.

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

chriscammers — Tue, 22 Aug 2017 20:58:16 GMT

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.

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

swuehl — Tue, 22 Aug 2017 23:06:37 GMT

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

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

dileepchalasani — Wed, 23 Aug 2017 12:35:30 GMT

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

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

sunny_talwar — Wed, 23 Aug 2017 12:36:41 GMT

Awesome

Please close the thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

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

dileepchalasani — Wed, 23 Aug 2017 12:40:35 GMT

Stefan,

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