SUM and COUNT returning incorrect results in straight table

[dillerja]

I have a straight table in which the only dimension is a field (TrimLine) that was generated using the Rowno() method in the load script.  I noticed some of of my totals and averages calculated within this table are incorrect as they seem to count some rows more then one time.  I have stripped this straight table down to just a few expressions and created a table box showing the same fields to show that there are no duplicate rows.  The below screen shot shows both of these and highlights one of the cells that are incorrect.  All of the values in the COUNT(Trimline) column should be 1.

Does anyone have any ideas on what could be causing this inconsistency?

[swuehl]

Using a table box does not ensure that these lines appear multiple times in your data model, a table box does show only distinct combinations of values (i,e, it will not represent the in-memory table in your data model when there are duplicate lines).

I assume your TrimLine do appear multiple times, or your fields are coming from multiple resident tables and you encounter some kind of implicte JOIN.

edit: could you post some more details, e.g. a sample qvw that demonstrate your issue, your script or a screenshot of your data model?

[danieloberbilli]

Seems that the table box shows the fields from what is supposed to be your fact table. The field TrimLine must be a key field and be linked to a dimension with 21 values. I might be mistaken, but as your sum(BirdsKilled) equals  21*514, it seems that your BirdsKilled is also part of the dimension table?

[qlikviewwizard]

Hi dillerja ,

Can You attach the sample file?

[#5]

try this >  Count(Distinct TrimLine)

[jagan]

Hi,

TableBox will not display the duplicate rows, so generate a Primary key column in your table and include that in Table box then you can see the difference.

Data:

LOAD

RecNo() AS Key,

*

FROM DataSource;

Now use Key field in the table box, if there is any duplicate row then you will see this.

Regards,

Jagan.

[dillerja]

Ok, so I found the offending code, however I still don't understand why it is behaving this way.  I started stripping the app down to its simplest part which still would exhibit the issue, and in that process I found that the highlighted left join below is causing my issue.  The purpose of this left join was just to add a new column (ABF_Indicator) which is derived by looking at values in an existing column.  I can just add this field to the above LOAD statement and that fixes my issue.  However, I still don't understand why this should cause the behavior I am seeing.  Is there something wrong about using a LEFT JOIN to add a column to an existing table like this with a resident load from the same table?  I agree that in this case it isn't necessary, but this feels like a QV bug to me.

[dillerja]

I did try adding a key using RecNo instead of RowNo, but it still didn't show any duplicates in the table box.

[dillerja]

Using Count(Distint TrimLine) does return the correct count, however I was just using the COUNT and SUM functions to demonstrate the issue that there is some kind of duplication happening within the data.  Using Distinct on SUM or AVG etc.

[#10 · Accepted solution]

Hi Jack,

if you have multiple rows of the same flock number then yes this is to be expected, as you are joining each flock number row to all the other rows of that flock number and duplicating up the rows.

Joe