Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How to Delete leading zeros

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

  

Old FieldNew Field
00123Null
345345
045Null
0000563Null
65896589
34573457
asddNull
fgfdgNull
1 Solution

Accepted Solutions
Highlighted

Re: How to Delete leading zeros

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

];

View solution in original post

13 Replies
Highlighted
Specialist
Specialist

Re: How to Delete leading zeros

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);

Highlighted

Re: How to Delete leading zeros

Hi Kirubakaran,

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

Highlighted

Re: How to Delete leading zeros

Is this you are expecting?

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted

Re: How to Delete leading zeros

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

Highlighted
Master III
Master III

Re: How to Delete leading zeros

Hi,

try

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

Regards,

Antonio

Highlighted
Creator III
Creator III

Re: How to Delete leading zeros

Hi Nagaraj,

Please use below expression,

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

Taj Mohamed

Highlighted
Contributor III
Contributor III

Re: How to Delete leading zeros

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

  

Current Field Output
00123Null
345345
045Null
0000563Null
56445644

LOAD * INLINE [

    Current Field

    00123

    345

    045

    0000563

    5644

];

Highlighted

Re: How to Delete leading zeros

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

];

View solution in original post

Highlighted
Not applicable

Re: How to Delete leading zeros

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