topic Re: How get RowNo() inside a module macro in QlikView

How get RowNo() inside a module macro

Tue, 29 Jul 2014 18:27:00 GMT

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?

Re: How get RowNo() inside a module macro

marcus_sommer — Tue, 29 Jul 2014 20:04:51 GMT

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

Re: How get RowNo() inside a module macro

rwunderlich — Tue, 29 Jul 2014 20:09:48 GMT

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

-Rob

Re: How get RowNo() inside a module macro

Tue, 29 Jul 2014 20:49:56 GMT

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

Re: How get RowNo() inside a module macro

Tue, 29 Jul 2014 20:53:10 GMT

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

Re: How get RowNo() inside a module macro

rwunderlich — Tue, 29 Jul 2014 22:15:47 GMT

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

Re: How get RowNo() inside a module macro

marcus_sommer — Wed, 30 Jul 2014 06:56:18 GMT

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

Re: How get RowNo() inside a module macro

Wed, 30 Jul 2014 14:40:03 GMT

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

Re: How get RowNo() inside a module macro

marcus_sommer — Wed, 30 Jul 2014 15:12:57 GMT

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

Re: How get RowNo() inside a module macro

rwunderlich — Wed, 30 Jul 2014 18:33:07 GMT

"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

Re: How get RowNo() inside a module macro

Wed, 30 Jul 2014 19:02:01 GMT

Hi, Marcus.

I understood what the code you send do. And I know that a system of logs is very useful.

But, note what I see:

1. the task of load data must include the code to record logs: makes working more complex;
2. any kind of update in order of load data, probaly, will denote changes in code: morr and more complex;

3. I need, at least, two "IFs" more to get the state of table;

4. to reuse the code, I need write it again or, if a build some kind of script macro

using $(variables), then, I will need check several parts of code to do not

interfere in others features.

If QlikView, yet, had no features to read the states of tables during the load process from a VBA or JavaScript module, is there a way to return a value from your own macros? I could see the statments SUB ... ENDSUB. Is there some statment to return a value to its caller?

Thanks.

...

Ricardo Ildefonso

Re: How get RowNo() inside a module macro

marcus_sommer — Wed, 30 Jul 2014 19:35:35 GMT

During the script-execution isn't any access through vba or js on tables and rows possible. Why want you put your checks in "extern" routines? Which adantage is expected? All your needs could be easily done with the table-functions and a few control-statements like if-then-else or sub-end sub, especially if you used further check-routines with filetime() or ErrorMode. There also the possibility to execute some batches or vbs during the reload so that you could check this etl-line from outside or you nested several etl-lines - but this could be very complicated ... generally all things are possible inside the script-execution.

The code to read these table-informations musn't be so large how in my example if TableName() and NoofRows() is enough and you could put these code in a include-variable or a sub. In summary: I think there isn't any other way to do this with lesser effort.

- Marcus

Re: How get RowNo() inside a module macro

rwunderlich — Wed, 30 Jul 2014 21:34:54 GMT

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

Re: How get RowNo() inside a module macro

Thu, 31 Jul 2014 13:17:59 GMT

Good morning, good day... Marcus and Rob.

Thanks for the help.

I will use the feature "variable with parameter"... It is powerful!!

In fact, I can see a lot of situations that it will be util.

About modules and macros, I have other features where they are necessary.

The code become simpler and I had tryed several types of ApplyMap and MapSubString that can't solve my needs of data sanitation.

But now I will use parameters variables to solve that stuff about records count.

Once more time: thans, thanks, thanks.

...

Ricardo Ildefonso