Null Values

[#1]

Hi

How to show '-' values as 'NA'.

Thanks

[Chanty4u]

hi use this

chk attached sample

=If(Len(Trim(Invoice.InternalDoc)) = 0, 'NA', Null())

[tresesco]

In the chart? Presentation tab-> Null Symbol/Missing Symbol - put 'NA'.

[Chanty4u]

sry replace

=If(Len(Trim(Invoice.InternalDoc)) = '-', 'NA', Null())

or else u can replace in  presentation tab

left down u will find  null symbol

[ashwanin]

Hi,

In Presentation tab under Null Symbol add  'Null' in place of '-'

[MayilVahanan]

Hi

It depends on where you want , in script means

Load *, If(Len(Trim(YourField)) = 0, 'NA', YourField) as YourField1 from yourtablename;

then use YourField1 in chart / listbox..

In Chart means,

Chart Properties -> Presentation tab-> Null Symbol/Missing Symbol -> 'NA'.

Hope it helps

[PradeepReddy]

you can handle NULL values in 2 ways..

1) Front End

Presentation tab-> Null Symbol/Missing Symbol-> Replace '-' with 'NA'.

2) Script Level

a) NULLDISPLAY - 

• To assign a value to NULL values, at table/sheet level

SET  NULLDISPLAY = '$ ';

• Applicable only for data comes from ODBC.

b) NULLVALUE

• The  NULL value fields effected are those fields included in the  NULLASVALUE statement.

SET NULLVALUE = '@';

   NULLASVALUE - Field Level

EX:       SET  NULLVALUE ='$';

NULLASVLUE  Field1, Field2;

Load Field1, Field2, Field3 from File1.CSV;

NULL Values in the fields Field1, Field2 will be replaced with '$'

c) If(Len(Trim(FieldName)) = 0, 'NA', FieldName) as FieldName1

Thanks,

Pradeep

[kkkumar82]

Adding to  pradeep,

If we are loading data from text or csv files we have to use

SET NULLINTERPRET='';

NULLASVALUE = field1,field2....

SET NULLVALUE - 'NA'

Thanks

Kiran Kumar

[jagan · Accepted solution]

Hi,

The best option is to deal this in script if possible like below

Data:

LOAD

If(Len(Trim(FieldName)) = 0, 'NA', FieldName) AS FieldName,

'

'

'

FROM DataSource;

OR

Chart Properties -> Presentation-> Null Symbol/Missing Symbol-> Replace '-' with 'NA' (This option will replace NA for all the fields)

OR

Calculated Dimension:

If(Len(Trim(FieldName)) = 0, 'NA', FieldName)

OR

Alt(FieldName, 'NA')

Hope this helps you.

Regards,

jagan.

[Kushal_Chawda]

See this

NULL handling in QlikView