Advanced: Remove leading zeros in Qlik Sense the right way

[JeromeNagarro]

Hi everyone! I read many posts here to remove the leading zeros from SAP ids if it's a number but never found the correct answer.

Many will fail in simple or more complex cases. The rule is that you must remove leading zeros in a code, if it's followed by digits [0-9] only. Else return the input code. The output must be a text only. The RegEx matching pattern could be something like ^0*(\d+)$|(.*). See https://regex101.com/ if you want to test other input codes.

Examples:

Load

  input,

  expected_output,

  YourFunction(input) as output

Inline [

  input|expected_output

  000000124789|124789

  abcdef|abcdef

  0001LMPn|0001LMPn

  01-23|01-23

  0000000000|0

  000004.1523|000004.1523

  000 4751|000 4751

  123,456,789|123,456,789

  -01562|-01562

  +2987001453|+2987001453

] (delimiter is '|');

What is "YourFunction" here so that output = expected_output for all inputs?

Thanks!

Edit: changed the delimiter in the inline load as Jbhappysocks suggested.

[edwin]

since a field can contain both text and number,  you can try this:

Load if(num(input)=input, num(input), input) as input Inline [
input, expected_output
000000124789, 124789
abcdef, abcdef
0001LMPn, 0001LMPn
01-23, 01-23
0000000000, 0
000004.1523, 000004.1523,
000 4751, 000 4751,
123,456,789, 123,456,789
-01562, -01562
+2987001453, +2987001453
];

the +298....  is i think a special case as +2 = 2

[jbhappysocks]

Tested the suggested script and the output didn't match expected output so had to give it a try, this at least solves it for example input, but I have a feeling there might be more exceptions, or that someone will come up with a neater solution 🙂

Load
input,
if(num(input)=input, num(input), input) as output,

text( //"The output must be a text only"
if(left(input,1)=0, //"The rule is that you must remove leading zeros in a code"
if(isnum(num#(input)), //"if it's followed by digits [0-9] only" No text input
if(frac(input)>0,input, //"if it's followed by digits [0-9] only" No decimals
num(input,'0')) // removes initial 0
,input), //"Else return the input code"
input)) //"Else return the input code"
as output2,
expected_output

Inline [
input| expected_output
000000124789| 124789
abcdef| abcdef
0001LMPn| 0001LMPn
01-23| 01-23
0000000000| 0
000004.1523| 000004.1523
000 4751| 000 4751
123,456,789| 123,456,789
-01562| -01562
+2987001453| +2987001453
] (delimiter is '|');

(Had to change to | to avoid 123,456,789 being split in the inline)

[edwin]

you really are talking about special cases which i think you need to analyze closely as there is a risk that not all special cases are defined and you will be left with a very convoluted expression.  you need to be able to negotiate a simpler requirement.  as i said +2 is a number which is 2.  converting 1,234,567 to a number that looks like 1,234,567 is a formatting of the number.  so you really are lumping a whole lot of concepts into one go.

maybe what you need is a secondary flag that tells you when you should perform a conversion or not - simplifies your expression a lot

[jbhappysocks]

Well, not that special really. We got two conditions presented:

• Either input starts with 0 or it doesn't
• Either input with starting 0 is followed by only 0-9, or it isn't.

And we got a pretty clear example of what output was expected for strange examples, like +2 or 123,456 etc. We can't start making up other outcomes as correct.

Based on conditions and expected output this I created a pretty straight forward formula and output matches expected output. All good.

But sure, as I also said, there might be other exceptions, so this solution is only true as long as the conditions are clear. But not is not really for me to say, I can only test with provided examples.

As I also said, I am sure someone can do better than I did and hopefully post here for all of us to learn from. 

Working day is over in Sweden now, time to grab a beer. Have a great weekend!

[JeromeNagarro]

As Jbhappysocks said, this doesn't work in many cases presented above.

[JeromeNagarro]

Thank you for your answer!

It looks great but it doesn't work with ThousandSep = ' ' (fr-FR locale settings) for the input '000 4751'. It returns '4751'. That's a shame because I can't wrap your answer in a neat function.