topic Remove Leading Zeros from Field while loading in QlikView

Remove Leading Zeros from Field while loading

Fri, 28 May 2010 12:55:36 GMT

Hello !

We have a field that is a numerical value with many digits.

AFAIK QV supports up to 11 or 12 digits for a field to be considered numeric.

I wanna load this field as String (since it can't be numeric cause it has too many digits) but REMOVE LEADING ZEROS while loading it.

This is a sample of the field values :


NUMCLI_COCTI ;
000000009279340;
000000008000040;
000000009424789;
000000009401191;
000000009372228;
000000009372228;
000000009021023;
000000009279340;
000000009221292;
000000009424789;
000000009401191;
000031230777806;
000599744000174;
[/CODE]
Is there a LTRIM ,with Zeroes instead of Spaces ? Kind of a RemLeft (Remove Left chars) ...
Any suggestions ?
Thanks !</body>

Remove Leading Zeros from Field while loading

sathishkumar_go — Fri, 28 May 2010 13:09:08 GMT

you want to use as string then use text function.

if want to remove all zero's are 1st 5 places????

because i noted some fileds r 1st 3 places only 0

-Sathish

Remove Leading Zeros from Field while loading

Fri, 28 May 2010 13:12:25 GMT

I dont know how many Trailing Zeroes we can have. The sample I provided has a coincidence. But it's not like that...

Remove Leading Zeros from Field while loading

sathishkumar_go — Fri, 28 May 2010 13:38:15 GMT

then u want to remove all zeors

ex:

0003000950 u want 395 or how u want?

-Sathish

Remove Leading Zeros from Field while loading

Fri, 28 May 2010 13:47:52 GMT

0003000950 I want 3000950

0000000001 I want 1

0000000012 then I want 12

123456789012345 I want 123456789012345

and so forth !

It's simple : REMOVE LEADING ZEROES FROM A FIELD !

Remove Leading Zeros from Field while loading

SunilChauhan — Fri, 28 May 2010 14:02:24 GMT

HI TRY THIS

HOPE THIS WILL WORK

REPLACE

(LTRIM(REPLACE(NUMCLI_COCTI, '0', ' ')), ' ', '0')

GOOD LUCK

Remove Leading Zeros from Field while loading

Fri, 28 May 2010 15:11:51 GMT

Hi Adriano

assuming your large number string has a fixed length you could split the field into subparts and create the numeric representation for each of the parts. The recombine the number, multiplying the higher parts with the according number.

You will still hit a border for the highest number representable.

num1 = num(left(NUMCLI_COCTI,8))
num2= num(right(NUMCLI_COCTI,8))
num = (num1 * 100000000) + num2

Regards

Jürg

Remove Leading Zeros from Field while loading

bullish35 — Wed, 01 Dec 2010 01:32:54 GMT

Hello. I can remove leading zeroes using this in the script, but second column (containing the non-zero value) is created in the table. Do you know how can I change the value within the existing column? Thank you.

Remove Leading Zeros from Field while loading

johnw — Wed, 01 Dec 2010 01:37:45 GMT

replace(ltrim(replace(YourField,'0',' ')),' ','0') as YourField

Remove Leading Zeros from Field while loading

Anonymous — Tue, 28 Feb 2012 22:46:18 GMT

ltrim(replace(Individual_Item_Number,'77','')) as Dupe So I used this variant to try and identify individual_items where they start with '77%' and return it's non 77 equivalent: Where ind_item = 12345, Dupe is 12345 as it should be. where ind_item = 7712345, Dupe is 12345 as it should be...however, if ind_item = 1277345, it returns 12345 when I would like it to remain 1277345. I only want it to act if the two left most digits meet the criteria. Thoughts?