topic No. of lines in a certain table (no. of rows) in QlikView

No. of lines in a certain table (no. of rows)

Wed, 27 Oct 2010 06:42:14 GMT

I have a straight table in one of the sheet. The straight table consist of 2 dimensions and 2 expressions. Each expressions has pretty long formula.

I created a summary sheet; intending to show the number of record found in the table mention earlier.

Originally, I use function "count" and try to combine the formula of the 2 expressions. It works but not all time. If I load the qlikview with different data set, the count does not always return the same number of lines in the straight table.

I wonder if there is any function that I can use to get the number of lines of a certain object in a qlikview?

Thanks.

Pound.

No. of lines in a certain table (no. of rows)

Wed, 27 Oct 2010 06:56:50 GMT

Hi,

For number of rows in a table you can use the function NoOfRows.

Regards,

Ravi

No. of lines in a certain table (no. of rows)

suniljain — Wed, 27 Oct 2010 07:04:34 GMT

you can use Noofrow or count(unique Filed in that table)

No. of lines in a certain table (no. of rows)

Wed, 27 Oct 2010 11:30:23 GMT

Guys,

The function NoOfRows is useful when I want to use this function in the same table itself.

The aim here is when you have 10 tables in 10 different sheets - I want to create a single summary sheet (like a dashboard you can say), that summarize number of records found on each of those 10 tables.

If selection is made; then the number of rows of the affected tables will be changed as well.

Thanks for replying though.

No. of lines in a certain table (no. of rows)

johnw — Wed, 27 Oct 2010 21:47:59 GMT

I'd guess something like this, assuming your table is suppressing null and zero:

sum(aggr(if(Expression1 or Expression2,1),Dimension1,Dimension2))

Replace "Expression1" and "Expression2" with your actual expressions from the straight table. Replace "Dimension1" and "Dimension2" with your actual dimensions from the straight table. To avoid repeating the expressions, consider storing them in a variable, and then using the variable in both places.

No. of lines in a certain table (no. of rows)

Anonymous — Thu, 28 Oct 2010 00:52:27 GMT

My preferens is to use macro with GetNoOfRows.
Should be triggerred by any select in this case

No. of lines in a certain table (no. of rows)

Thu, 28 Oct 2010 07:12:40 GMT

Hi John,

When there's no selection, the value of the formula given does not show the correct number of rows from the straight table.

However, if I made a selection (for example by choosing a certain period), the result of the formula match all the time. I have 10 years to select and any combination so far results in accurate way.

But when no selection - the result differ.

Any other idea?

No. of lines in a certain table (no. of rows)

Thu, 28 Oct 2010 07:15:32 GMT

Hi Michael,

Now even a more 'newbie' question for you..

I did created a macro before (the macro was found in this forum too); but I can't refer the variable created inside a macro and put that in my dashboard.

How do I pass the variable from macro to my sheet objects?

No. of lines in a certain table (no. of rows)

Anonymous — Thu, 28 Oct 2010 13:49:17 GMT

Just refer by the variable name. You can show, for example, a text box with text like this (in the name of your variable is Var):
=Var
or
='Number of rows in table 1 = ' & Var
In chart expressions you can refer to it as Var, or $(Var).

No. of lines in a certain table (no. of rows)

johnw — Thu, 28 Oct 2010 17:54:11 GMT


poundesville wrote:
Hi John,
When there's no selection, the value of the formula given does not show the correct number of rows from the straight table.
However, if I made a selection (for example by choosing a certain period), the result of the formula match all the time. I have 10 years to select and any combination so far results in accurate way.
But when no selection - the result differ.
Any other idea?<div></div>

Hmmm, not sure why it's not working when there are no selections. But Michael's solution sounds more robust than what I was proposing anyway since it will refer to the actual object instead of trying to duplicate what the object is doing. So I'd just do that instead, I think.

No. of lines in a certain table (no. of rows)

Fri, 29 Oct 2010 02:35:32 GMT

Thank you John and Michael.

I marked both your replies as "answer"

For those searching the same thing.

1. Create a variable. From menu -> Settings -> Document settings -> Variable tab. Just add and give it any name. For my macro, the variable name is "myVar"

2. Select the macro type and click on Edit Module

3. Enter macro like this


poundesville wrote:

Sub NoOfRows
 set chart = ActiveDocument.GetSheetObject("CH77")
 ActiveDocument.Variables("myVar").SetContent chart.GetNoOfRows, true
end Sub

4. On Macro page. on Document Event Trigger choose "OnAnySelect" and choose the macro "NoOrRows" that you just created in step 3

5. Now you can use "myVar" anywhere in your qlikview.

No. of lines in a certain table (no. of rows)

Anonymous — Thu, 20 Jan 2011 18:15:36 GMT

Tried this macro and it doesnt work for me. I get a message the the object does not support the property. I looked in the api guide and i dont see and GetNoOfRows associated with charts. Hmm.

No. of lines in a certain table (no. of rows)

Anonymous — Tue, 01 Feb 2011 16:33:47 GMT

Tim,
Waht type of chart is it? GetNoOfRows exists for straight table only.
(Unless it is different depending on QV version as well)

No. of lines in a certain table (no. of rows)

christian77 — Tue, 01 Feb 2011 17:42:26 GMT

Hi:

rowno() gives you the number of the row you are.

If you have more than 1 dimension, it initializes every change of dimension. Use then rowno(Total) and youll have the rowno without considering dimensions.

It works the same for columnno(), columnno(Total). Very useful to inter record calculations like accumulating any column or row together with rangesum and range any function.