topic Re: Loading files from filelist in .qvd in QlikView

Loading files from filelist in .qvd

yura_ratu — Thu, 10 Apr 2014 12:29:36 GMT

Hello

I have a list of filenames as a column in a qvd file. How can use them for importing data in a loop such as

FOR Each File in FileList ()

Thanks

Re: Loading files from filelist in .qvd

daveamz — Thu, 10 Apr 2014 13:24:23 GMT

Hello Yuriy,

FileNamesTable:

LOAD

Concat(Chr(39)&filenames&Chr(39), ', ') as filenames

FROM ....

LET vFileNames = Peek('filenames', 0, 'FileNamesTable');

DROP TABLE FileNamesTable;

FOR Each vFile IN $(vFileNames)

$(vFile):

LOAD *

FROM $(vFile);

NEXT vFile;

Best regards,

David

Re: Loading files from filelist in .qvd

yura_ratu — Thu, 10 Apr 2014 14:25:54 GMT

Hello David

Could you please explain what Peek function does here.

LET vFileNames = Peek('filenames', 0, 'FileNamesTable');

Currently in variables overview vFileNames is empty

Re: Loading files from filelist in .qvd

maxgro — Thu, 10 Apr 2014 15:01:14 GMT

here (second example) a possible answer

http://community.qlik.com/blogs/qlikviewdesignblog/2013/09/02/loops-in-the-script

Re: Re: Loading files from filelist in .qvd

daveamz — Fri, 11 Apr 2014 08:51:49 GMT

For using

FileNamesTable:

LOAD

Concat(Chr(39)&filenames&Chr(39), ', ') as filenames

FROM ....

you should get a table named 'FileNamesTable' with only 1 row containing values field 'filenames' concatenated like 'Field1', 'Field2', 'Field...'

peek function gets the value of this row and put it into vFileNames variable.

Because, you have 2 identical tables, QlikView auto-concatenate them and that's the reason your peek function doesn't work.

Here, I attached an example.

Best regards,

David

Re: Loading files from filelist in .qvd

nizamsha — Fri, 11 Apr 2014 09:03:45 GMT

u will excatly get what u want only specify the ur path location in the path location path

For each file in Filelist( 'pathLocation\*.qvd')

Load *,

'$(file)' as FileName,

Floor( FileSize('$(file)')/1024) as FileSize,

SubField( '$(file)','\',-1) as FileType

From [$(file)];

Next file

Re: Loading files from filelist in .qvd

daveamz — Fri, 11 Apr 2014 09:29:42 GMT

Yes, but sometimes you want to load a list of SQL table names to extract data in a loop. For dimension tables for instance.

David