How does QV count rows in pivot tables?

Mon, 18 Jun 2012 20:01:21 GMT

Hello again,

I have a list of around 36,000+ emails. If I create a listbox of the emails, it shows in the status bar, bottom right, that there are 36,208 entries selected. If I create a pivot table that shows company name, email, and an expression "=count(Total emailfield)", it shows in the table 36,208 entries, but if I look in the status bar, it now shows 36083 x 3 rows. If I clone that table, and change the expression to "=count(distinct total emailfield)", it shows 36,067 in the expression field in the table, but again in the status bar shows 36083x3 rows.

From this, I assume I have 141 duplicate email entries, but haven't been able to find any through inspection. But where does the "36083" number come from? In all cases, I have selected to NOT suppress null values, and to print "n/a" or "missing" for Null or missing values, respectively, so I don't think it's a question of QV counting entries that I've hidden. How does QV count rows, and why am I getting different numbers?

Re: How does QV count rows in pivot tables?

hic — Tue, 19 Jun 2012 07:50:16 GMT

What you have of the status bar for a list box is not the same as that for a pivot table. For the list box, the status bar shows distinct values (D: 1/36208) and number of records in the table where this field is found (F: 1/36208). These numbers could be different due to NULL values or duplicates.

For a pivot table, this number shows the number of rows in the pivot table itself. Which again could be different due to NULL values or duplicate values in the dimension or in the key between the tables; that is if the dimension field and the expression field reside in two different data tables.

Bottom line: It can be a complex issue...

Do the following: Investigate these numbers for the following cases and see when they change:

A) Use "emailfield" as dimension and Count(emailfield) as expression. (1 dim, 1 expr) Check for duplicates!

B) Use another field from same table as "emailfield" as dimension and Count(emailfield) as expression. (1 dim, 1 expr)

C) Use field from other table as dimension and Count(emailfield) as expression. (1 dim, 1 expr)

D) Use several fields as dimensions and Count(emailfield) as expression. (N dim, 1 expr)

You can also look for duplicates using Count(emailfield) - Count(distinct emailfield)

HIC

topic How does QV count rows in pivot tables? in QlikView

How does QV count rows in pivot tables?

Re: How does QV count rows in pivot tables?