Strange Behavior Removing Leading Zeros

MuratErcan1 — Sat, 27 Aug 2022 10:18:33 GMT

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

Re: Strange Behavior Removing Leading Zeros

BrunPierre — Sat, 27 Aug 2022 19:54:41 GMT

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

topic Strange Behavior Removing Leading Zeros in QlikView

Strange Behavior Removing Leading Zeros

Re: Strange Behavior Removing Leading Zeros