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: 
Not applicable

How get RowNo() inside a module macro

Hi, people.

I need get the row count of tables from a module Function.

The editor isn't integrated into object-model and I look for any

reference about Application.Tables or ActiveDocument.Tables, etc.

Than, I try a lot of expression., but nothing works.

I would like something like:

Function RecordCount(vanTableName)

dim d

     d = Application.RowNo(vanTableName)

     RecordCount = d

End Function

How can I get this using modules?

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

1. You can't access table properties from a VB routine via script.

2. The function to get number of rows in a table already exists as a script function "NoOfRows('tablename')".

3. Script SUBs can't return values like functions.

4. If you want to modify NoOfRows so it returns -1 instead of null for empty table, you can create your own version as a variable with parameter like this:

SET myNoOfRows = alt(NoOfRows('$1'),-1);

and call it in script as:

$(myNoOfRows('mytable'))

-Rob

View solution in original post

13 Replies
marcus_sommer

I'm not sure if it's possible per api - but you could look in the APIGuide,qvw in your install-folder.

If you really need these information within the macro-modul I would create a structure-table with the system-fields like $Tables and $Fields and $Rows and so on. There is already an object-typ available by right-click on an empty sheet-part and then structure-table but you could also create some own objects. Then I would read these table per macro - look in APIGuide in field Member to getrowcount and you will find some examples.

- Marcus

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Where will you use this module function? In script or in a chart expression?

-Rob

Not applicable
Author

Hi, Marcus.

Yes, I am buinding a library to control the update of some tables but I won't have the names of its before the process..

I will try your sugestion and look for the member GetRowCount. If I get my objective I will tell you.

Thanks for your answer.

...

Ricardo Ildefonso

Not applicable
Author

Hi, Rob.

I just wrote above about the use. It is inside a module, not in a tipical script and not in a chart.

Do you have some idea?

Thanks for your attention.

...

Ricardo Ildefonso

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I don't understand. What I was asking is: from where will the function be invoked? Even if it is invoked from another routine within the module, what environment (chart, script or external) was the first routine invoked in?

-Rob

marcus_sommer

Hi Ricardo,

how Rob Wunderlich mentioned it's important to know where these informations should be generated and why?

Within the gui per module-editor you could use my suggestion from above, should it be while the script-execution it won't work because at that time there isn't an ActiveDocument available - but then you could use table-functions like TableName() or Noofrows() within the script. It is from extern you could read the meta-data from your app.

It is only for a single applications or for many? So it depends from what you want ...

- Marcus

Not applicable
Author

Goog morning, Marcus. Good morning, Rob.

Sorry I wasn't clear. I really am new on QlikView.

And, just reading your messages, I learned some new: while it is running a Reload there is no active document.

So, I know I have a problems from basics. I am senior in SQL, C# and more, but I believe that work with QlikView is more like ABAP... Am I rigth about that?

Ops! I don't have experience with ABAP, too.

I am writing a code to be reused in other tasks and apps.

So, I don't have idea about when a script will be called. Just for it, each part of code is within a SUB ... ENDSUB declaration.

At the end of scripts a just use CALL to invoke each one I need.

Because that, there are moments I will start a process that need a table load before.

So, I need a function like this:

Function RecordCount(value)

    Dim r: r = -1

    ON ERROR RESUME NEXT

    r = Application.NoOfRows(value)

    RecordCount = r

    ON ERROR GOTO 0

End Function

In that case, if a table just was loaded, but have no records I will get 0 (zero) for answer. But, if that table never was loaded before, the expression must fire a internal error and I will get a impossible value to records counter: -1 !!!

Can you see the big diference?

This test is repeated several times during the load process. Answer your question, Rob, I will use it from a script, at moment of load my data.

It would be very, very unproductive and very unsafe use variables for control that.

Just for all these reasons I need know about a table state and its properties at a module macro.

May I clear this time?

I am sorry about my "newbie" situation, but I need start from a point, am I not?

Thanks your dedication.

...

Ricardo Ildefonso

marcus_sommer

Hi Ricardo,

for this you don't need macro-functions. How above mentioned you could read these informations with script-table-functions and you could use variables or a own table to save these informations. And check-routines about the record-counts or any errors (therefor is an Errormode available - set ErrorMode = 0|1|2) could be made with if-then-else-statements. Here a small example from an old post Re: Auswertung - QlikView Enterprise Management Console how table-functions could be used - but I believe you won't be able to access these post. In this case I log loads into a qvd whereby most of these load-statement is in a include-variable outsourced:

let vTabName = 'Pull-Load'; // Name des Script-Tabs für die LoadLogging-Routine

let vLoadArt = 'none'; // Variable um die Load-Art (full, incremental, partiell, none) abzubilden
let vRelated = $(vRelated) + 1; // Zählvariable, um mehrere Loads an einem Tag unterscheiden zu können

let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging

xyz:
Load x, y, z From z;
store xyz into abc.qvd (qvd);

/****************************************************************************************************************************************************************
Load-Logging zur Verbesserung der Performance und zum Nachhalten der Load-Vorgänge
  *****************************************************************************************************************************************************************
Hintergrund:
- das QlikView-eigene Load-Logging ist nicht konfigurierbar und vom Format unübersichtlich, insbesondere wenn mit Loop's und Call's gearbeitet wird
- Log-Files sind zur weiteren/dauerhaften Verwendung schwierig einlesbar
Vorgehensweise:
- relevante Loaddaten werden überwiegend per Tabellen- und Zeitfunktionen in Variablen geschrieben und in ein Log-File geschrieben
- der Script-Code wird größtenteil in eine Include-Variable ausgelagert, um die Übersichtlichkeit des eigentlichen Scriptes zu bewahren
****************************************************************************************************************************************************************/


let vLoadTable = TableName(NoOfTables() - 1); // Name der zuletzt geladenen Tabelle wird ausgelesen
let vLoadEnde = num(now() - today(), '#.########', '.', ','); // Ende des Load-Vorganges als Uhrzeit
let vLoadFieldCount = NoOfFields('$(vLoadTable)'); // Anzahl der Tabellenfelder wird ausgelesen
let vLoadFields = ''; // Variablen-Inhalt löschen

for i = 1 to $(vLoadFieldCount)
let vLoadFields = '$(vLoadFields)' & FieldName($(i), '$(vLoadTable)') & ', '; // Felder der Tabelle werden String verkettet
next

LoadLogging:
Load * From LoadLogging.qvd (qvd); // historische Load-Logging-Daten werden eingeladen
     concatenate
Load
today(1) as Datum, time($(vLoadStart), 'hh:mm:ss') as Start, time($(vLoadEnde), 'hh:mm:ss') as Ende, time($(vLoadEnde) - $(vLoadStart), 'hh:mm:ss') as Dauer,
'$(vLoadTable)'
as Tabelle, NoOfRows('$(vLoadTable)') as Datensätze, $(vLoadFieldCount) as FeldAnzahl, '$(vLoadFields)' as FelderListe, rowno() as DS,
DocumentName() as Anwendung, if(isnull(filesize('$(vLoadTable).qvd')), 'kein Store', filesize('$(vLoadTable).qvd')) as FileSize, $(vRelated) as Related,
'$(vTabName)'
as ScriptTab, '$(vLoadArt)' as LoadArt
AutoGenerate 1;
store LoadLogging into LoadLogging.qvd (qvd);
drop table LoadLogging;

/****************************************************************************************************************************************************************
Ende Load-Logging
  ****************************************************************************************************************************************************************/

- Marcus

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

"I really am new on QlikView."

Ricardo,

I can't help thinking you are totally on the wrong track. It's unusual to need macros/module at all, esp for a beginner.

I think Marcus is giving you some good pointers. You can write reusable script Subroutines using the script SUB statement. And you can pull those routines into multiple scripts usng the $(Include) statement.

-Rob