Count() Incorrect Total

[quilldew]

Hi,

So I have table which has a unique field in it. Lets call that field 'ID'.

Count(ID) shows 176.

Count(Distinct ID) shows 175.

In the table viewer hovering over the table shows 176 entries.

ID is a Key Field, and I'm only expecting to see 175 of them. I assume I've made a mistake with my joins so I create a tablebox and add all the fields from that table (and only fields from that table). 

I extract the tablebox to Excel. 175 rows. Eh what?

I have no idea why one ID has duplicated itself, and is being counted in the main table, but I can't get it to show up.

Any ideas what I might be doing wrong. I can't supply a qvw sorry sensitive data and I can't replicate the problem on any other document. :S

[Vegar]

I would have created a chart/table with ID as dimension and Count(ID)-1 as measure. It will pinpoint which ID is duplicated in your data.

[jonathandienst]

A straight count (no DISTINCT) on a key field is generally not recommended. The key field is populated from all tables containing that field. In your case, the associated table two records containing the same ID value, so the count will count that instance as two, even though it only corresponds with a single row in the first table.

[quilldew]

Thats true, I should have given a better example. I didn't actually perform the count on the ID, I performed it on another field in the same table that 'should' be unique.

When I create a table with only that field as a dimension it only shows me one row, but the count is 2. I can then add another dimension (doesn't happen with all dimensions), and it will change the count to 1. I haven't changed anything in the expression, I've just added another dimension, and the total has changed. 

[quilldew]

Hi,

I can see the ID that is duplicated by including the ID as a dimension. I then filter on that ID, giving me a count of 2, but it doesn't matter which other dimension I add from that same table, I can't get it to show me more than 1 row, even though the count is still 2. I can even make the count show 1 by only adding certain non key fields as a dimension. I've never seen anything like it.

[quilldew · Accepted solution]

Okay I've figured the problem out. It was a problem with one of the datasets I pulled in and joined to the main table, it had 2 lines with the same ID I was joining on.

I'm still confused as to why the tablebox didn't show both lines though. I get that both lines had exactly the same data, but as Qlikview knew there were 2 lines with the exact same data it should surely have showed them both?

I wouldn't have known if I'd not performed a count and already knew exactly what the total should be. For someone with a count (not on a key field) that totals in the millions, meaning they wouldn't be able to eyeball a straight table and know the count was off, this could be a problem.

[Vegar]

A table box does only show distinct combination. If you have 100 identical rows in your data set the table box will only present it as one single row. Consider it as a multidimensional listbox, the listbox does also only show one instance of each value.

[quilldew]

Ahhhh, that makes sense thank you.