Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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)
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
Well, not that special really. We got two conditions presented:
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!
As Jbhappysocks said, this doesn't work in many cases presented above.
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.