Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pranali_gawde
Partner - Contributor III
Partner - Contributor III

Remove blank from number field

Hi Sir,

I have blank value in my data for a number field.I am unable to remove that blank from field .

please help me to remove blank.

below is the snapshot.

1 Solution

Accepted Solutions
pranali_gawde
Partner - Contributor III
Partner - Contributor III
Author

Thanks everyone for ur help. i removed blank using this expression

If(len(trim("SEGMENT CODE"))= 0 or "SEGMENT CODE"='NULL' or "SEGMENT CODE"='-', Null(), "SEGMENT CODE" ) as ValueNullConv,

View solution in original post

7 Replies
avkeep01
Partner - Specialist
Partner - Specialist

Hi Pranali Gawde,

In the script you can replace the [OS SEGMENT CODE] field with

IF(LEN([OS SEGMENT CODE])>0[OS SEGMENT CODE],'#Unknown') AS [OS SEGMENT CODE]

pranali_gawde
Partner - Contributor III
Partner - Contributor III
Author

No this is not working .

avkeep01
Partner - Specialist
Partner - Specialist

Hi pranali Gawde,

Then the value isn't empty. Maybe it exists of some spaces. Try using TRIM to get rid of the spaces.

IF(LEN(TRIM([OS SEGMENT CODE]))>0[OS SEGMENT CODE],'#Unknown') AS [OS SEGMENT CODE]

giovanneb
Creator II
Creator II

Hi, use the trim command together


IF(LEN(TRIM([OS SEGMENT CODE]))>0[OS SEGMENT CODE],'#Unknown') AS [OS SEGMENT CODE]

rajivmeher
Creator
Creator

Hi pranali.gawde

Did you tried using Expression in dimension as =NUM( [OS SEGMENT CODE]) ?


This should eliminate values other than numbers.


Please see the screenshot for reference.


Regards

Rajiv

balabhaskarqlik

Post the script of this field: [OS Segment Code]

Try script like this:

Table:

LOAD

     name,

     date,

     title

FROM ABC

WHERE isnull(date)=0;

Or

Load ... From File.xls (biff, ...) Where Len(Trim(Field)) > 0 ;

pranali_gawde
Partner - Contributor III
Partner - Contributor III
Author

Thanks everyone for ur help. i removed blank using this expression

If(len(trim("SEGMENT CODE"))= 0 or "SEGMENT CODE"='NULL' or "SEGMENT CODE"='-', Null(), "SEGMENT CODE" ) as ValueNullConv,