Number of rows in straight/pivot table into a variable?

[#1]

I want to count the number of rows in a straight table (and also, in other applications, in pivot tables).

I went to the API cookbook, and it suggested:

SET table = ActiveDocument.GetSheetObject("CH03")

vTabHigh = table.GetRowCount

I thought this would be a script expression, but it's not. It's clearly not a chart expression, so I tried creating a macro:

Function TabHeight(tableID)

SET workingtable = ActiveDocument.GetSheetObject(tableID)

TabHeight = workingtable.GetRowCount

END Function

That was accepted by the macro editor, but it didn't work either - always returned NULL.

I want to know the number of rows so I can suppress printing in another program if number of rows = 0.

Thanks,

Kevin

[hic]

I would do this in a different way: If you assign a variable a string value and the string starts with an equal sign, QlikView will evaluate what comes after the equal sign. See http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/04/the-magic-of-variables

This means that if you assign the variable

     '=Count(Distinct X)'

the variable will always show the number of values of X. In a chart, the number of rows is defined by the dimension(s). So, if you have X, Y and Z as dimensions, you can calculate the number of rows in the chart by

     '=Count(Distinct X & Y & Z)'

HIC

[#3]

I've seen this statement before, and it confuses me: "The number of rows is defined by the dimensions. " I have plenty of tables with three dimensions (company, user name, and last login date) that have hundreds of rows. Not sure how this computes!

Also, the lack of complete syntax is troubling: if my dimensions are Company, User and LLD, is the syntax:

count( DISTINCT 'Company'&'User'&'LLD'), or is it:

count( DISTINCT Company&User&LDD)?

thanks, Henric.

[hic]

The number of rows is indeed defined by the dimensions. QlikView creates the Cartesian product of their values and looks at the possible combinations = count( DISTINCT Company & User & LDD). See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/25/dimensions-and-measures

On the syntax: Never use singe quotes for field names. Single quotes are for field values. Use double quotes or brackets (these are equivalent) for field names. See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/09/quoteology

HIC

[swuehl]

Henric,

what about if some rows are suppressed when zero? Do you have an idea for a good solution except replicating in advanced aggregation?

(I assume table.GetRowCount will return the row count excluding the suppressed rows, right?)

Another 0.02€ on the syntax, what confused me from time to time at the beginning was, that QV expects field names in single quotes with some functions (e.g. fieldvalue() )...

Regards,

Stefan

[hic]

Q: "...suppressed by zero..."

A: No, you need to create a condition in your aggregation function.

Q: "...field names in single quotes..."

A: You are right. That is indeed confusing... and I am not sure that we have been consistent over the years when implementing different classes of functions. The original idea was clear: Field Names in double quotes and Field Values in single quotes. But then we needed a way to denote field names also when they were out-of-scope - e.g. Peek() in the script but outside a load statement - so in some cases you should write also field names with single quotes. But I am not sure I want to defend single quotes in Fieldvalue()...

HIC

[#7]

1 - This "help" system is very frustrating to use! It's difficult to search for content, and simple 'cut and paste' doesn't seem to work - where is a guide on how to use it?!

2 - Back to the issue at hand:

I am trying to count the number of rows in a PIVOT TABLE in my document, which changes whenever a new date is selected OR the data is reloaded. After much wailing and gnashing of teeth, I created a macro, which apparently I cannot just copy and paste into this box. However, the macro works fine - I assigned it to a button, and when I click it, the variable in my document is updated. 

Now, I want the macro to run whenever EITHER of these conditions is satisfied:

1 - document is reloaded, or

2 - a selection is made in the "Call Date" field.

I have tried MANY combinations of triggers, singly and in groups - OnActivate  (sheet trigger), OnAnySelect, (document event trigger), OnSelect   (field event trigger). NOTHING works. The only way the macro runs and the variable gets updated is if I click the button. this is not acceptable, as I need the variable to be updated when the data is reloaded and the current date is selected.

QUESTIONS:

1  - Does having the macro assigned to a button invalidate the other triggers? (i.e. remove the button!)

2 - Is there some other setting that needs to be applied at the document/sheet/user level to make macros run as

     a result of sheet/document/field triggers?

Thanks,

Kevin

[Anonymous]

I see that the issue in subject has been resolved - it would be better for you to close it and open a new one for the macro triggering.  You'll get more readers this way.

Next, have you tried to assign the macro to the OnPostReload event?  Looks like this is what you need.  You can also assign it to OnOpen event.
As for the field selection, use "Call Date" field, event OnSelect.

Having the macro assigned to a button has nothing to do with it.

[#9]

Michael:

Thanks for your suggestions - I didn't put it in my previous post, but I also tried OnPostReload.

I tried "OnSelect" for the Call Date field (as noted above).

None of these work. I have a text box in the page with the value of the variable,

and it only changes when I click the button, not when I leave/return to the page,

not when I select/change the Call Date, not when I reload the data. (I don't

just rely on the text box - I go to Settings -> Variable Overview -> and check

it there as well.)

I am literally spending hours on something that should be extremely simple!

There must be SOME other setting that needs to be turned on, because I'm

doing everything that's suggested, and NOTHING is happening - the variable

is only updated IF AND ONLY IF I hit the macro button. (I'm using CAPS

because I'm receiving suggestions that I have already tried).

[Anonymous]

Try with "Statistics Bos" and select "Total Count"

It should work