topic Re: Remove blank from number field in QlikView

Remove blank from number field

pranali_gawde — Wed, 25 Nov 2020 16:16:04 GMT

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 — Thu, 30 Aug 2018 13:00:32 GMT

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 — Thu, 30 Aug 2018 13:06:43 GMT

No this is not working .

avkeep01 — Thu, 30 Aug 2018 13:09:46 GMT

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 — Thu, 30 Aug 2018 13:18:00 GMT

Hi, use the trim command together

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

rajivmeher — Thu, 30 Aug 2018 14:56:42 GMT

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 — Thu, 30 Aug 2018 15:35:51 GMT

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

Try script like this:

Table:

LOAD

name,

date,

title

FROM ABC

WHERE isnull(date)=0;

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

pranali_gawde — Fri, 31 Aug 2018 05:35:26 GMT

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,