topic Re: Load Data from multiple files and different folders in QlikView

Load Data from multiple files and different folders

Wed, 19 Oct 2011 20:21:33 GMT

Hi there, I`m new in QlikView;

Does anyone know how can I load data from a paradox database splitted in many files into different folders.

All the transactions files are splitted in months. Each month is represented by one folder. For example for one fiscal year i have

12 subfolders and 12 invoice files in each subfolder. With the below script I can load transactions from multiple files but i must define in "set folderscope"

statement the path for each subfolder. In all the cases the number of subfolders cannot be defined and I can`t use the script in current form.

Also i need to read the name of the subfolder. I cannot use the filedir() function inside the SQL statement. Any ideas?

ODBC CONNECT TO Paradox;

directory 'D:\DATA\company\';

set FolderScope = 'D:\DATA\Company\2011_05','D:\DATA\Company\2011_06';

FOR each FilePath in $(FolderScope)

LOAD *;

SQL SELECT *

FROM $(FilePath)\invoices.db;

NEXT FilePath;

thks in advance

Load Data from multiple files and different folders

fosuzuki — Thu, 20 Oct 2011 20:09:41 GMT

Hi,

try using a loop with the DirList function:

for each Dir in dirlist ('c:')

'do something here

next Dir

(found this in the help, using the 'For each..next' keyword)

Hope this helps you

Regards

Fernando

Re: Load Data from multiple files and different folders

Tue, 25 Oct 2011 13:23:13 GMT

Thx Fernando for the hint

It is working like this:

FOR each Dir in dirlist ('D:\path\*')

LOAD *;

SQL select

FROM $(Dir)\table.db;

NEXT Dir

I still have problemes with reading the name of the subfolder...

I tried to use in the load statement filedir() function but it shows me nothing

Any ideas for this issue?

Best Regards,

Mihai