topic Re: NullCount() returns 1 in QlikView

NullCount() returns 1

Samuel — Sat, 16 Nov 2024 05:08:02 GMT

Recently I noticed QlikView behavior that drawn my attention. Consider the following code:

Men:
LOAD

	Id as Men.Id,
	Id as Key.Id,
	Name as Men.Name
	
INLINE [

	'Id', 'Name'
	'1', 'Arthur'

];

Women:
LOAD

	Id as Women.Id,
	Id as Key.Id,
	Name as Women.Name

INLINE [

	'Id', 'Name'
	'1', 'Elizabeth'
	'2', 'Amanda'

];

Elizabeth is a lucky woman while Amanda was left with no partner. This leads to interesting consequence. After loading this script, with no selections, "NullCount(Women.Id)" will return "0" while "NullCount(Men.Id)" will return "1". Can someone explain me why, in this scenario, chart expressions finds row of NULLs in "Men" table?

Re: NullCount() returns 1

marcus_sommer — Tue, 27 Nov 2018 08:13:10 GMT

I think the reason is that nullcount() is an aggregation-function and needs a dimension against the calculation could be performed - within the script-load it are the fields within the group by statement and within the UI it takes the key-fields from the table it belongs to.

- Marcus

Re: NullCount() returns 1

ckarras22 — Wed, 28 Nov 2018 15:08:19 GMT

Hello,

The "issue" appears from the load script. As it is your script, there is an implicit outer join, therefore although at the Symbol table level (Men) you haven't any nulls, at the "Table" level you have. This is apparent in your chart.

Christos K.