How do i count an empty field?

[Anonymous]

Hi All

Need help please with this. Thanks!

How to sum or count fields from an Excel spreadsheet in Qlikview that have no data in the fields?

Adil

[its_anandrjs]

If it is an associated with any field or primary key then you can count easily or if you have sample so provide that.

Suppose you have null field name Product

Then counting null value as

Count(if(Len(Product)=0,Product) it will give you null value count

And another way is

Count( If( Isnull(Product) = -1, Product)

[Anonymous]

I have a vehicles table

most is complete although some other fields are input the vehicle is left blank.

I want to count how many fields were left blank in the Vehicles table

Thanks

[#4]

Simple use

Count(If(Isnull(Field) or Len(Trim(Field))=0,1))

[MK_QSL]

Better to create a Flag in your script....

IF(LEN(TRIM(YourFieldName))=0 or ISNULL(YourFieldName), 1, 0) as Flag

Now Simply use below in any expression to count empty or null field

COUNT({<Flag = {1}>}YourFIeldName)

Or

SUM(Flag)

[mambi]

in your load script you can use for exemple :

SET NullValue = 'ND';

NullAsValue NameOfField;

in your text object count(ND)

[jonathandienst]

Hi

Depends whether they are loading as blanks or nulls. By default, an empty field from a spreadsheet will load as a blank (assuming that the field is called Vehicle:

     Count({<Vehicle = {''}>} Vehicle)

If its null, then

     NullCount(Vehicle)

HTH

Jonathan

[Roop]

I would tend to give default values to blank fields so that they can easily be counted by selection.

So in your load have something like Manufacturer which may be blank:

if Len(Trim(Manufacturer)) = 0,

     'Unknown Manufacturer',

     Manufacturer

) as Manufacturer, ......

Although this seems a big task I always find that it helps later on when developing your application.