topic Re: Loading all files from single folder into QV document in QlikView

Loading all files from single folder into QV document

Tue, 27 Aug 2013 13:50:16 GMT

Good morning all,

I have created a single QV document where I load three separate documents, each slightly different from each other. The load works fine and everything is good.

Now The manager wants to load new data from a new report each day. It is not possibnle to just generate a new report with all the data, the report has a thirty one-day size limit and the manager wants to keep all the historic data so the new report generated each day will be the previous day's work and will have to be added to the all the previous reports.

So I will accumulate these reports in the following folders:

Skill
Volume
MRLS

These folders correspond to the three separate loads in the load script.

I have seen a few articles here regarding loading all docs from a single folder or even sub folders. My question is this, since I have three separate loads, each with slightly different rules from each other as the base reports are slightly different, will I need to modify each load independently?

I have attached a copy of the QV and the source docs. I have the source files arranged in a directory like so:

LP Files
- Volume
- MRSL
- Skill

Each sub-folder includes two files currently with a new one to be added each day. The three sub-folders correspond to the three loads in my load script.

What is the best way to approach this and what is the correct script to use?

Thanks in advance,

Steve

Re: Loading all files from single folder into QV document

Gysbert_Wassenaar — Tue, 27 Aug 2013 17:34:54 GMT

You can use the subroutine from the script Henric posted in this discussion and call that subroutine three times for each subfolder. If you change the line FileList: into $(Root): you will get tables named after the subfolder you passed in as parameter. If you want you can later rename these tables if other names make more sense. Oh, and you want to change the extension to .xls. Something like this:

sub ScanFolder(Root)

Set vConcatenate = ;

for each FileExtension in 'xls'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

$(Root):

$(vConcatenate)

LOAD *, '$(FoundFile)' as SourceFile

FROM [$(FoundFile)] (biff, embedded labels, table is [Sheet1$]);

Set vConcatenate = Concatenate;

next FoundFile

next FileExtension

end sub

Call ScanFolder('Skill');

Call ScanFolder('Volume');

Call ScanFolder('MRLS');

Re: Loading all files from single folder into QV document

Tue, 27 Aug 2013 18:06:50 GMT

Actually, I found a very simple way to do this.

I had already made sure that all the reports would be loadedwith standardized header, column names, etc. In the folder where they will be stored as source files, I made sure that they all had the same file name with a number appended.

Then in the load scrip where the path/filename is for the load file, I just substituted a wildcard (*) for the file number and Voila! QV loads all the files with that file name.

Thanks for the help.

Steve

Re: Loading all files from single folder into QV document

Fri, 11 Oct 2013 10:21:36 GMT

i am getting error in

$(vConcatenate).

please help me how to reslove this