topic Re: Advanced: Remove leading zeros in Qlik Sense the right way in App Development

Advanced: Remove leading zeros in Qlik Sense the right way

JeromeNagarro — Sun, 05 Feb 2023 18:23:30 GMT

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.

Re: Advanced: Remove leading zeros in Qlik Sense the right way

edwin — Fri, 03 Feb 2023 13:51:47 GMT

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

Re: Advanced: Remove leading zeros in Qlik Sense the right way

jbhappysocks — Fri, 03 Feb 2023 14:43:34 GMT

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)

Re: Advanced: Remove leading zeros in Qlik Sense the right way

edwin — Fri, 03 Feb 2023 15:15:59 GMT

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

Re: Advanced: Remove leading zeros in Qlik Sense the right way

jbhappysocks — Fri, 03 Feb 2023 16:13:04 GMT

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!

Re: Advanced: Remove leading zeros in Qlik Sense the right way

JeromeNagarro — Tue, 07 Feb 2023 11:04:09 GMT

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

Re: Advanced: Remove leading zeros in Qlik Sense the right way

JeromeNagarro — Tue, 07 Feb 2023 11:14:15 GMT

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.