topic How to get a simple count of rows in a table in QlikView

How to get a simple count of rows in a table

peter_turner — Wed, 23 Jun 2010 11:22:39 GMT

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.

How to get a simple count of rows in a table

Wed, 23 Jun 2010 13:53:54 GMT

Hi Peter

The RowNo() function should provide this, so you can create your caption such as:

="This is my title, and there are this many rows: " & RowNo()

RowNo() without any parameters should give you total number of rows in the table.

Good luck,

How to get a simple count of rows in a table

peter_turner — Wed, 23 Jun 2010 14:10:36 GMT

Hi Nigel,

Thanks for the reply, that was the sort of thing i was looking for - however i cannot get it to work, it allways returns 1.

I've used rowno() in the past in scripts to assign i row ID, but in the developer it seems to only return the number 1.

Peter.

How to get a simple count of rows in a table

Fri, 24 Sep 2010 17:00:36 GMT

I'm having trouble with this function as well. Were you ever able to find an answer to this?

How to get a simple count of rows in a table

Fri, 24 Sep 2010 17:09:43 GMT

Hi there, try using the total qualifier, like this:

rowno(total)

That should do the trick.

Regards

How to get a simple count of rows in a table

Tue, 16 Nov 2010 17:21:43 GMT

That worked! Thank you!!!

How to get a simple count of rows in a table

Fri, 26 Nov 2010 15:47:32 GMT

When I have tried to use either RowNo(total) or NoOfRows(total) in the chart caption, it always comes back with 1. Add it as an expression in the chart and it shows the correct value.

Any ideas?

Thanks

SV:Re: How to get a simple count of rows in a table

johnan — Sat, 27 Nov 2010 08:09:31 GMT

Hi,

You can try to set a counter in the script:

'1' as RowCounter

Very simple

SV:Re: How to get a simple count of rows in a table

prieper — Sat, 27 Nov 2010 08:58:52 GMT

Why not using a simple count on a field, which is available in all records?

='This table has ' & COUNT(Keyfield) & ' records'

HTH
Peter

SV:Re: How to get a simple count of rows in a table

Mon, 29 Nov 2010 21:21:09 GMT

Thanks for that, but I am wanting to count the number of records displayed in the table.

The expressions has a formula which sets unwanted records to null thereby not showing certain records. I wanted to show the total number of records actually shown. The same would apply if a table is shown and one or more fields have the 'Omit row when field is null'

Any suggestions

SV:Re: How to get a simple count of rows in a table

peter_turner — Wed, 08 Dec 2010 22:13:54 GMT

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.

How to get a simple count of rows in a table

Thu, 28 Apr 2011 14:38:09 GMT

Anyone ever find a solution to this?

How to get a simple count of rows in a table

suniljain — Thu, 28 Apr 2011 14:45:56 GMT

you can use

count(Primarykey) as XXX

How to get a simple count of rows in a table

christian77 — Fri, 29 Apr 2011 14:22:20 GMT

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)

How to get a simple count of rows in a table

Wed, 12 Oct 2011 12:20:43 GMT

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

How to get a simple count of rows in a table

SunilChauhan — Wed, 12 Oct 2011 13:24:47 GMT

use noofrows()

see the help for no of rows

How to get a simple count of rows in a table

Wed, 12 Oct 2011 13:31:55 GMT

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.

Re: How to get a simple count of rows in a table

Wed, 12 Oct 2011 15:52:56 GMT

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

How to get a simple count of rows in a table

Mon, 24 Oct 2011 14:36:55 GMT

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

How to get a simple count of rows in a table

Mon, 24 Oct 2011 15:22:10 GMT

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.