topic Re: Null Values in QlikView

Null Values

Mon, 21 Mar 2016 04:51:28 GMT

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

Thanks

Re: Null Values

Chanty4u — Mon, 21 Mar 2016 04:56:56 GMT

hi use this

chk attached sample

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

Re: Null Values

tresB — Mon, 21 Mar 2016 04:57:03 GMT

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

Re: Null Values

Chanty4u — Mon, 21 Mar 2016 04:59:15 GMT

sry replace

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

or else u can replace in presentation tab

left down u will find null symbol

Re: Null Values

ashwanin — Mon, 21 Mar 2016 05:04:58 GMT

Hi,

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

Re: Null Values

MayilVahanan — Mon, 21 Mar 2016 05:07:18 GMT

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

Re: Null Values

PradeepReddy — Mon, 21 Mar 2016 05:20:26 GMT

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

Re: Null Values

kkkumar82 — Mon, 21 Mar 2016 05:43:37 GMT

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

Re: Null Values

jagan — Mon, 21 Mar 2016 06:25:29 GMT

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;

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

Calculated Dimension:

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

Alt(FieldName, 'NA')

Hope this helps you.

Regards,

jagan.

Re: Null Values

Kushal_Chawda — Mon, 21 Mar 2016 06:29:34 GMT

See this

NULL handling in QlikView

Re: Null Values

Mon, 21 Mar 2016 06:49:50 GMT

Thank You All

Re: Null Values

Mon, 21 Mar 2016 08:10:29 GMT

Is it possible to apply background color for 'NA'?

Re: Null Values

Chanty4u — Mon, 21 Mar 2016 08:16:58 GMT

yes u can do .

if it used in stright or pivot table

u can use custom format cell option in design grid vie.

Re: Null Values

Chanty4u — Mon, 21 Mar 2016 08:17:56 GMT

chk dis samples

How to dyanamically change the individual cell color os a Pivot table

Custom Format Cells in Straight Table

Re: Null Values

jagan — Mon, 21 Mar 2016 08:20:50 GMT

Hi,

Try like this

If(Len(Trim(DimensionName)) = 0, Color1, Color2)

Hope this helps you.

Regards,

Jagan.

Re: Null Values

jagan — Mon, 21 Mar 2016 08:21:47 GMT

Hi,

If you handled in script like below

Hi,

Try like this

LOAD

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

FROM ...;

If(DimensionName= 'NA', Color1, Color2)

Hope this helps you.

Regards,

Jagan.

Re: Null Values

MayilVahanan — Mon, 21 Mar 2016 08:21:59 GMT

Yes, its possible

Background color Expression

=if(Len(Trim(YourFieldName)>0, White(), Red())

Ex:

Project Number	Customer A/c Name	Sum([Sales Revenue])
		80449.24
1234567		21956.44
1234567	Black	13290
1234567	Jones	22576.6
1234567	Smith	22626.2