Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
julianschmuckli
New Contributor II

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?

Tags (3)
Labels (3)
1 Solution

Accepted Solutions
julianschmuckli
New Contributor II

Re: Get count of multiple files loaded in one statment

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); 
2 Replies
MVP & Luminary
MVP & Luminary

Re: Get count of multiple files loaded in one statment

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

 

julianschmuckli
New Contributor II

Re: Get count of multiple files loaded in one statment

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);