How to Delete leading zeros

[Anonymous]

Requirement is to delete fields starting with zeros and make it as NULL. Also keep NULL for alpha-numeric values.

Old Field	New Field
00123	Null
345	345
045	Null
0000563	Null
6589	6589
3457	3457
asdd	Null
fgfdg	Null

[micheledenardi]

LOAD

  [Old Field],

  if(left([Old Field],1)=0,null(),num([Old Field])) as [New Field]

FROM [Cartel1.xlsx] (ooxml, embedded labels, table is Foglio1);

[tamilarasu]

Hi Kirubakaran,

If(Left([Old Field],1) = 0 or IsText([Old Field]),Null(),[Old Field]) as [New Field];

[Anil_Babu_Samineni]

Is this you are expecting?

[sunny_talwar]

May be just Num(Num#(OldField)) as NewField

[antoniotiman]

Hi,

try

LOAD [Old Field],
If(Num([Old Field])=Text([Old Field]),[Old Field]) as New

Regards,

Antonio

[tajmohamed30]

Hi Nagaraj,

Please use below expression,

=trim(replace(OldField,0,' '))

Taj Mohamed

[Anonymous]

My question was Replace rows to 'NULL' which starting with Zero

Current Field	Output
00123	Null
345	345
045	Null
0000563	Null
5644	5644

LOAD * INLINE [

    Current Field

    00123

    345

    045

    0000563

    5644

];

[sunny_talwar · Accepted solution]

Something like this

Table:

LOAD *,

  If(Left([Current Field], 1) = 0, 'Null', [Current Field]) as Output;

LOAD * INLINE [

    Current Field

    00123

    345

    045

    0000563

    5644

];

[#10]

Hello Kirubakaran,

I recommend you to take a look at the examples for the logical functions IsNum and IsText in the qlikview help.

PS: Don't forget to mark answers as helpful or correct.

,Greetz Hannah