topic Problem with count function in QlikView

Problem with count function

Mon, 07 Dec 2009 21:55:00 GMT

I'm working with only one table in my model.

I created a table object and made some selection. My object table show me 9 lines in the table.

I created a text objects which give me the number of line =count(field1). The result of his count is 11 and not 9.

Can you help me ?

AW:Problem with count function

Mon, 07 Dec 2009 21:58:24 GMT

Have you tried =count(distinct field1)?

Problem with count function

Oleg_Troyansky — Mon, 07 Dec 2009 22:01:53 GMT

This one of very common puzzles people face in the beginning... In our mind, we think that QlikView stores tables (just the way we see them in the Table Viewer, and that Table Box shows table contents row by row...

In reality, QlikView stores distinct fields and only shows tables for our convenience. Table box shows associated field values, but not the rows of the table.

In your case, you must have some of your data rows duplicated more than once. Table box will not show 2 distinct rows if all the values are identical.

To test it, you can add a new field in your data load and use function "RowNo()" to populate the field. This will generate a unique row number for each loaded data row. Add this new field to the Table Box, and you should see 11 rows now, some of them will be identical, except for the Row Number...

good luck!

Problem with count function

Mon, 07 Dec 2009 22:23:50 GMT

Thanks for your answer. I try to make the test.

Problem with count function

Tue, 20 Apr 2010 14:01:43 GMT

Oleg to the rescue again. Thanks I was racking my brain on this!