Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
peter_turner
Partner - Specialist
Partner - Specialist

How to get a simple count of rows in a table

Hello Everyone,

I'm trying to find a simple way to make my table title show the number of rows in the current selection.

Usually I just count one of my ID fields such as count(MY_ID), but I'm looking if there is a built in function I can use instead?
Similar to the "Sum of Rows" feature for an expression, just need a simple count of the number of rows that are shown on screen.

Does QV have a built in function for this sort of thing or anyone have any ideas?
The dataset is large with 6million+rows.


Thanks,
Peter.



30 Replies
peter_turner
Partner - Specialist
Partner - Specialist
Author

Hi Andrew,

It sounds like we have the same problem.

Its to do with how QV 'hides' rows on a table, for my example this is because all the columns have the same value so QV shows the row once instead of x times.
If I added my unique ID key field to the table it would show all rows and of course the count of rows would match.


This behaviour is fine inside QV logic, except that I only want to count the rows that are actually visible on the table, QV would class them as NON unique rows.

Let me know if you figure this one out!

Peter.

Not applicable

Anyone ever find a solution to this?

suniljain
Master
Master

you can use

count(Primarykey) as XXX

christian77
Partner - Specialist
Partner - Specialist

The thing is that rowno() initializes itself to 1 every change in the first dimension.

If you have more than one dimension you need to use rowno(Total)

Not applicable

I am having this exact problem.  Has anyone ever figured out a way to make this work?

SunilChauhan
Champion
Champion

use noofrows()

see the help for no of rows

Sunil Chauhan
Not applicable

I can use NoOfRows(TOTAL) in the body of the chart, and it works fine.

However, when used in the caption:

NoOfRows() always returns 1.

NoofRows(TOTAL) always returns 1.

There has to be a way to to display the number of rows diplayed in the caption, or alternatively, in a separate Text object.

Not applicable

Here's a working solution from a QV friend. The credit goes to him, not me.  (Thanks, Gus!)  Hope this helps others with similar issues.
I wanted the number of visible rows in a straight table to be displayed as a dynamic part of the table caption.
There is one dimension in the straight table, calculated as:
=if(DischargeDispositionID = 'E',AccountNumber))
The Caption is calculated as follows:
='Mortality Detail: '& Count(distinct if(DischargeDispositionID = 'E',AccountNumber)) &' Patient Deaths'
Where the table displays 11 lines of data, the caption reads:
Mortality Detail:  11 Patient Deaths

Not applicable

thanks for your answer but i have 1 more question :

how to do when we have more than 1 dimension ?

for exemple I have 2 dimensions :

the first one: =if(TYPE_ACTIF='FCPE', LIBELLE_FOND)

the second dimension : CODE_FOND

What is the expression to count the number of rows ??

i've tried Count(distinct Expr1, Expr2) but it doesn't work..

thanks for your help

Not applicable

The same QV friend suggested this:

Count(distinct dim1 & dim2 & dim3, etc)

I have not been able to make that work, but give it a try.  He suggested wrapping the calculated dimensions in parenthises.