use number of lines fetched

[#1]

Hello all, I want to use the number of lines fetched you see in the 'script execution progress' window for all load statements further on in a script, for example to put in a logging table, I guess there must be some internal variable where this is stored, can anyone help me out here? Thnx in advance!

Regards,

Sander

[SunilChauhan]

in script u write

rowno() as record_fetched

ex:

Load

a,

b

...

.

-

-

rowno() as record_fetched

from abc.xls

fetch_record will give u no of record fetched

[#3]

I do not want this one because I use a distinct in the load statement, I just want to have the total figure available after the load statement is finished and then for example write it to a table.

[#4]

And i guess it would be recno() in stead of rowno(), for example if you use a where clause.

[rwunderlich]

NoOfRows('MyTable')

-Rob

http://robwunderlich.com

[#6]

Rob, Thanks for your reaction! but this is also not what I'm looking for, this gives me the number of the records in table which is the result of the load, and not the number of rows in the source;

Example:

Table: abc.csv

ProductNmbr, ProductName

1, A

1, A

2, B

2, B

3, C

4, D

4, D

Script

Product:
Load Distinct *
From C:\abc.csv
Let SourceRecords = NoOfRows('Product');

Here I get the value 4 for the variable SourceRecords, but I want the value 7.

Can anyone help me out here?

[rwunderlich]

I understand. I don't know of any clean way to do this without re-reading the source. Which is probably not what you want to do.

You can read the logfile of the running script, but that sounds a bit messy.

-Rob

[#8]

Hi there, i think you can do it like this:

Table:

LOAD count(@1) as Count

FROM

test.csv

(txt, codepage is 1252, no labels, delimiter is ',', msq);

let vRowsNo = peek('Count',0,'Table');

drop table Table;

Regards

[yakir_manor]

i wanted a log table to log my stuff so i did it like so: (you can see how you can get the number of lines fetched)

     LET vStartTime = Now();
     // Log Table Creation or load
     LET vLogTable_NotExists = IsNull(QvdCreateTime('$(vFilePath).log.csv'));
     IF $(vLogTable_NotExists) = 0 THEN
       ScriptLog: // load the existing table
       LOAD * FROM $(vFilePath).log.csv (txt, utf8, embedded labels, delimiter is ',', msq);
     ELSE
       ScriptLog: // create table if no log table exists
       LOAD
       now() AS TimeStamp,
       'General' AS Type,
       'Create CSV Log' AS Message
       AUTOGENERATE(1);
     ENDIF

     // add row
     LET TimeStamp = now();
     //ScriptLog:
     LOAD * INLINE [
     TimeStamp,Type,Message
     $(TimeStamp),General,Reload Start
     ];

     // load some table
     YourTable:
     LOAD * Inline [
     Col1,Col2
     11,12
     21,22
     ];

     // get number of line fetched

     LET LineFetched = NoOfRows('YourTable');

     // log number of line fetched
     LET TimeStamp = now();
     LOAD * INLINE [
     TimeStamp,Type,Message
     $(TimeStamp),Line Fetched from YourTable,$(LineFetched)
     ];

     // add two rows
     LET vEndTime = Now();
     LET vDuration = Interval(Timestamp#('$(vEndTime)') - Timestamp#('$(vStartTime)'), 'hh:mm:ss');
     LET TimeStamp = now();
     LOAD * INLINE [
     TimeStamp,Type,Message
     $(TimeStamp),Run Elapsed,$(vDuration)
     $(TimeStamp),General,Reload End
     ];
     // store the table in csv
     STORE ScriptLog INTO $(vFilePath).log.csv (txt);
     // dump variables so they dont appear on doc variables
     LET vFilePath =;
     LET vStartTime =;
     LET vEndTime =;
     LET vDuration =;

[Anonymous]

Did anyone find a solution to this one?