Remove blank from number field

[pranali_gawde]

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.

[avkeep01]

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]

No this is not working .

[avkeep01]

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]

Hi, use the trim command together

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

[rajivmeher]

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

[BalaBhaskar_Qlik]

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 · Accepted solution]

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,