Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Get count of multiple files loaded in one statment

I want to get a total amount of all rows in multiple files, which are saved as a QVD. Actually, with one file I would accomplish this like that:

data:
LOAD count(id) AS counter FROM data.qvd (qvd);
LET number = Peek('counter');

Of course, I know that I also can use RowNo() or Count() the whole table in one command, but I want to try this with that solution.

Now when I try to fetch multiple files in one statement, as shown below, I always get only the count of the last loaded file and not the total:

data_multiple:
LOAD count(id) AS counter FROM data_*.qvd (qvd);
LET number_multiple = Peek('counter');

Now my question is how do I get the full amount of rows and not only the last one.

What I tried so far

I already tried to rearrange the statement like this:

data:
LOAD id FROM data_*.qvd (qvd);

LOAD Count(id) AS counter Resident data;
LET number = Peek('counter');

But I do get still the same result. Is there some way how to achieve this?

Labels (1)
1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Ah nice, thank you very much. I have updated it so that it counts the number of rows in total.

let total_number = 0;

for each file in filelist('D:\Data\data_*.qvd') 
QVDRecords: load QvdNoOfRecords('$(file)') as Counter, '$(file)' as Source autogenerate 1;
total_number = total_number + Peek('Counter');
next

trace QVD: $(total_number); 

View solution in original post

2 Replies
marcus_sommer

I suggest to read these information from the qvd meta-data, maybe in this way:

for each file in filelist('D:\Data\data_*.qvd')
   QVDRecords: load QvdNoOfRecords('$(file)') as Counter, '$(file)' as Source autogenerate 1;
next

- Marcus

 

Anonymous
Not applicable
Author

Ah nice, thank you very much. I have updated it so that it counts the number of rows in total.

let total_number = 0;

for each file in filelist('D:\Data\data_*.qvd') 
QVDRecords: load QvdNoOfRecords('$(file)') as Counter, '$(file)' as Source autogenerate 1;
total_number = total_number + Peek('Counter');
next

trace QVD: $(total_number);