Strange Behavior Removing Leading Zeros

[MuratErcan1]

Hi all,

Recently I came across a strange behavior, and wanted to check forum whether there is a solution.

In QlikView Script, I want to load below 2 text records  and remove leading zeros.

However, whatever I try, I keep getting strange results. I am using the below script:

T1:
LOAD
    MAT,
    Text(MAT) AS MAT_TXT,
    Replace(Ltrim(Replace(Text(MAT), '0', ' ')), ' ', 0) AS MAT_NoZeros_1,
    Replace(Ltrim(Replace(MAT, '0', ' ')), ' ', 0) AS MAT_NoZeros_2
;
LOAD * INLINE[
    MAT
    0195-0012-10
    0195-0012-010
];

I am getting the below as the result.  "0" from the "010" is also dropped.

I know that Qlik engine stores "010" and "10" as one value when it is a numeric field, but in this case it is a text field and 010 is not even in the beginning.

I guess Somehow, "-" character is causing Qlik engine to store values as separate numeric values??

Even more strange is, the behavior changes when there is only one "-" character. when I load below 2 entries instead, result is as expected. "010" and "10" of these strings are interpreted correctly.

    

I have tried replacing "-" with "|", removing leading zeros and then replacing "|" back to "-".  Removing leading zeros worked with "|" character, but when replace them with "-", bam! leading zeros starts appearing again. 

T1:
LOAD
    MAT_NoZeros_3,
    Replace(MAT_NoZeros_3,'|','-') AS MAT_NoZeros_4
;
LOAD
    Replace(Ltrim(Replace(
        Replace(Text(MAT),'-','|')
    , '0', ' ')), ' ', 0) AS MAT_NoZeros_3
;
LOAD * INLINE[
MAT
    0195-0012-010
    0195-0012-10
];

Any thoughts on how to handle this? Any help is much appreciated!

Thanks,

Murat

[BrunPierre · Accepted solution]

Just wrap the expression around with Text() to ensure it is treated as such.

Text( Replace(Ltrim(Replace(Text(MAT), '0', ' ')), ' ', 0) ) AS MAT_NoZeros_1