Table Box missing rows

[#1]

I have what is essentially a bunch of general ledger data in QV11. My data model is pretty simple, one table with 20 or so columns.

When I filter the data through the use of selection fields, my pivot table shows the correct sum(amount) over all the resulting accounts. I have a tab called DATA that I use in debugging that is just a Table Box with 10-15 columns of data. If I display a Text Object with count(amount) I get 478. Another Text Object with sum(amount) I get say $100,000.00. My pivot table show $100,000.00 in the title bar where I show sum(amount). However, if I export theTable Box to Excel or hand count the rows, the hand count and the count in Excel shows 298 records and $99,876.72. I can't seem to figure out why the table box is missing data. Is it only showing unique rows of the subset of columns that I selected? Actually I checked that by selecting all possible columns and I actually ended up with a few more rows and a different sum(amount).

I checked the source data from the accounting system and it ties exactly to the sum(amount) and count(amount) values so I'm confused. Is there some filtering that the Table Box is doing?

[#2]

I've had the same problem.

I think the table object actually shows distinct set of rows. It seems it is making like a group by with all the columns and not really all records.

I do not know if this is an error or an expected behavior

[jagan · Accepted solution]

Hi,

Table object displays only distinct rows.  I think your data has duplicate records.  To eliminate this load your table with a new column like RowNo(), now include this column as dimension in Table object.  Now it displays all rows.

Sample script

TableName:

LOAD

     RowNo() AS ID,

     *

FROM DataSource;

Regards,

Jagan.

[swuehl]

You' re right, it only shows unique rows of the subset of columns you selected.

This is often confusing people, but I think it's by design.

You could use a record ID field in your source table to get around this issue, include this record ID in your table box.

(If you don't have one yet, you can create a new field during load

...

recno() as RecordID,

...

)

Or use a straight table chart and add count() and sum() expressions, so you're data will still show only one line per unique rows of columns you selected, but you get the information how many records do show this unique row (and the sum of amount if needed.

Hope this helps,

Stefan

[#5]

RowNo() worked perfectly. It is frustrating that the general ledger system that we use is not configured, at least in the view I have, to show me a unique transaction ID for each entry. We have tons of entries that are identical (I would not say duplicate) in my view yet they are all correct.

I chose RowNo because it does not consider where clauses although I got the same results with RecNo.