topic Re: How to skip the row in a table, when field is null? in QlikView

How to skip the row in a table, when field is null?

Fri, 05 Dec 2014 04:43:46 GMT

Hi QV Team,

I've a Serial # field in a table, I need to skip the entire row when the Serial # record is Missing/Null.

Help me out !!

Re: How to skip the row in a table, when field is null?

anbu1984 — Fri, 05 Dec 2014 06:00:59 GMT

In Script

Load * From Table where Len(Trim([Serial #])) > 0;

Re: How to skip the row in a table, when field is null?

its_anandrjs — Fri, 05 Dec 2014 06:06:49 GMT

Hi,

If you want to remove it from the chart you can use

Suppress When Value Is Null option

And you can read more on the NULL Handelling

NULL handling in QlikView

Regards

Anand

Re: How to skip the row in a table, when field is null?

Fri, 05 Dec 2014 06:12:55 GMT

Hi Anand,

Where is this option " Suppress when Value is NULL" on Table box object ?

Re: How to skip the row in a table, when field is null?

its_anandrjs — Fri, 05 Dec 2014 06:20:46 GMT

In Table box it is not available it is in charts only you can remove it in the load script then and read that columns

You can create the filters in load script or completely remove it as Anbu suggest from loadscript

lOAD iF(len(ColA) > 0,'Null','NotNull') AS NullFlagA,

iF(len(ColB) > 0,'Null','NotNull') AS NullFlagB,

ColA,ColB;

LOAD * Inline [

ColA,ColB

A,5443

B,56

,45

D,54

F,54 ];

Regards

Anand

Re: How to skip the row in a table, when field is null?

amit_saini — Fri, 05 Dec 2014 06:32:59 GMT

Try something like below:

=if (not isnull(Plant),Plant )

Thanks,
AS

Re: How to skip the row in a table, when field is null?

its_anandrjs — Fri, 05 Dec 2014 06:36:19 GMT

Hi,

In place of the Table box you can use the straight table also by adding all the dimension and in expression write 1. And use option Suppress When Value Is Null from chart Properties >> Dimensions >> Select Null dimension >> Tick mark Suppress When value is null OR From Presentation >> Suppress Zero-Values tick mark all this if you do not want to do change in the load script.

Regards

Anand

Re: How to skip the row in a table, when field is null?

amit_saini — Fri, 05 Dec 2014 06:42:00 GMT

Try this also:

=if(Len(Trim(Your_Feild))>0 , Your_Field)

Thanks,

Re: How to skip the row in a table, when field is null?

its_anandrjs — Fri, 05 Dec 2014 06:54:53 GMT

Or you can use the single filter also

iF(len(Trim(ColA)) > 0 and len(Trim(ColB)) > 0,'Null','NotNull') AS NullFlagA,

Regards

Anand