Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
];
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);
Hi Kirubakaran,
If(Left([Old Field],1) = 0 or IsText([Old Field]),Null(),[Old Field]) as [New Field];
Is this you are expecting?
May be just Num(Num#(OldField)) as NewField
Hi,
try
LOAD [Old Field],
If(Num([Old Field])=Text([Old Field]),[Old Field]) as New
Regards,
Antonio
Hi Nagaraj,
Please use below expression,
=trim(replace(OldField,0,' '))
Taj Mohamed
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
];
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
];
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