Aggr and distinct function

[vajid4525]

Hi Team,

I wrote a Qlik expressioncount( distinct [Vehicle Number])and count(aggr(count([Vehicle Number]),[Vehicle Number])).

Logically both of them should provide a count of distinct values. But it's showing exceptional cases for data in the attached QVD.

count( distinct [Vehicle Number])-->1

count(aggr(count([Vehicle Number]),[Vehicle Number]))-->2

When I do group by for the attached QVD with Vehicle Number the number of lines fetched is 2

LOAD [Vehicle Number],count([Vehicle Number])
as count
FROM
[Qlik_team_Final.qvd]
(qvd) group by [Vehicle Number];

But if I convert this QVD to CSV file and try to do the same group by, the number of lines fetched is 1

LOAD [Vehicle Number],count([Vehicle Number])
as count
FROM
[Qlik_team_Final.csv](txt, utf8, embedded labels, delimiter is ',', msq) 

group by [Vehicle Number];

Can someone please help to get this clarified?

Thank You

[justISO · Accepted solution]

Hi, first of all, in your attached QVD field [Vehicle Number] one value is null, other is 'non-breaking space' (or chr(160)). It is not the same, so with group by you get 2 values. I guess (but you should test), when converting to csv, this chr(160) is removed and group by just give you one line with null value?

Other thing, count() is not counting null values, so it is correct that 'count( distinct [Vehicle Number])-->1' as only value it is see is this 'space'. Once again, I guess, that 'count(aggr(count([Vehicle Number]),[Vehicle Number]))-->2' basically works as countcount: you aggregate count per [Vehicle Number] and get values 0 (for null) and 1 (for space). And you count these values (0 and 1) again, so you get 2 as they are 2 different values (something like count( [Vehicle Number]) + NullCount([Vehicle Number])).

However, this is just my dubious opinion. Perhaps someone could clarify or even refute this.