Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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. 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
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
Where will you use this module function? In script or in a chart expression?
-Rob
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
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
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
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
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
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
"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