topic How do i count an empty field? in QlikView

How do i count an empty field?

Anonymous — Thu, 17 Apr 2014 11:47:34 GMT

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

Re: How do i count an empty field?

its_anandrjs — Thu, 17 Apr 2014 11:50:31 GMT

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)

Re: How do i count an empty field?

Anonymous — Thu, 17 Apr 2014 11:55:14 GMT

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

Re: How do i count an empty field?

Thu, 17 Apr 2014 11:58:12 GMT

Simple use

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

Re: How do i count an empty field?

MK_QSL — Thu, 17 Apr 2014 12:02:07 GMT

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)

SUM(Flag)

Re: How do i count an empty field?

mambi — Thu, 17 Apr 2014 12:02:39 GMT

in your load script you can use for exemple :

SET NullValue = 'ND';

NullAsValue NameOfField;

in your text object count(ND)

Re: How do i count an empty field?

jonathandienst — Thu, 17 Apr 2014 12:04:35 GMT

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

Re: How do i count an empty field?

Roop — Thu, 17 Apr 2014 14:33:39 GMT

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.