topic Re: load files with specific names from folder through for loop in QlikView

load files with specific names from folder through for loop

markgraham123 — Thu, 12 Jan 2017 17:22:53 GMT

Hi all,

I'm trying to load 5 files from the folder of 1000 files.

I have stored file names in vFilenames.

vFilenames = abg, dhjk, qwe, qwee, gsdf;

How can i load only these 5 files from the folder, instead of loading all files and then filtering by the filebasename()?

FOR EACH File in FileList('$(vFolder)\$(vFilenames).QVD') //Storing filenames in the variable 'File'

Table1:

LOAD *

FROM [$(File)] (qvd);

The above is not working.

Any help is highly appreciated.

Re: load files with specific names from folder through for loop

rwunderlich — Thu, 12 Jan 2017 17:57:51 GMT

What do you mean, not working? Script Error? Can you post the expanded load statement from the document log and the error message?

-Rob

Re: load files with specific names from folder through for loop

sunny_talwar — Thu, 12 Jan 2017 17:58:19 GMT

Look at the first example here:

https://help.qlik.com/en-US/sense/3.1/Subsystems/Hub/Content/Scripting/ScriptControlStatements/For%20Each.htm

// LOAD the files 1.csv, 3.csv, 7.csv and xyz.csv

for each a in 1,3,7,'xyz'

LOAD *

from file$(a).csv;

next

Re: load files with specific names from folder through for loop

sunny_talwar — Thu, 12 Jan 2017 17:59:45 GMT

I think Rob, the issue is that vFilenames expand to this:

FOR EACH File in FileList('$(vFolder)\abg, dhjk, qwe, qwee, gsdf.QVD') //Storing filenames in the variable 'File'

Table1:

LOAD *

FROM [$(File)] (qvd);

NEXT

Which is not what he wants, I believe

Re: load files with specific names from folder through for loop

markgraham123 — Thu, 12 Jan 2017 18:00:29 GMT

Rob,

The filename were not being read / the file names have to be expanded individually.

Re: load files with specific names from folder through for loop

sunny_talwar — Thu, 12 Jan 2017 18:01:54 GMT

So for you, may be this:

FOR EACH File in 'abg', 'dhjk', 'qwe', 'qwee', 'gsdf'

Table1:

LOAD *

FROM [$(vFolder)\$(File).qvd] (qvd);

NEXT

Re: load files with specific names from folder through for loop

markgraham123 — Thu, 12 Jan 2017 18:03:41 GMT

Sunny,

Do we have to hard code the file names?

I was trying to store file names in variable.

As the file names keep changing.

Re: load files with specific names from folder through for loop

sunny_talwar — Thu, 12 Jan 2017 18:06:32 GMT

Not tested, but may be like this:

SET vFilenames = 'abg', 'dhjk', 'qwe', 'qwee', 'gsdf';

FOR EACH File in $(vFilenames)

Table1:

LOAD *

FROM [$(vFolder)\$(File).qvd] (qvd);

NEXT

Re: load files with specific names from folder through for loop

markgraham123 — Thu, 12 Jan 2017 19:16:39 GMT

Sunny,

It is not working:

LET vFilelist = peek('DateKey'); //I'm readin this from another table. This has the dynamic list of file names

SET vFolder = '\\Folder\';

FOR EACH File in $(vFilelist)

Table1:

LOAD *

FROM [$(vFolder)\$(File).qvd] (qvd);

The file names are not getting read by the script in the for loop

Re: load files with specific names from folder through for loop

sunny_talwar — Thu, 12 Jan 2017 19:18:33 GMT

Is this a list or a single date?

Re: load files with specific names from folder through for loop

markgraham123 — Thu, 12 Jan 2017 19:23:18 GMT

It is reading file name as :

\\Folderpath\File1, File2, File3, File4 etc..

In which case, we need it to be:

\\Folderpath\File1

\\Folderpath\File2

\\Folderpath\File3

\\Folderpath\File4

Re: load files with specific names from folder through for loop

markgraham123 — Thu, 12 Jan 2017 19:23:34 GMT

list

Re: load files with specific names from folder through for loop

sunny_talwar — Thu, 12 Jan 2017 19:30:08 GMT

Can you share the script that creates DateKey before this Let statement

LET vFilelist = peek('DateKey');

Re: load files with specific names from folder through for loop

markgraham123 — Thu, 12 Jan 2017 19:32:35 GMT

Load concat(Distinct Date(%DateKey,'YYYYMMDD')&'Orders',',') as DateKey

Resident Table1;

o/p of vFilelist in my report:

20160101Orders,20160102Orders,20160103Orders,20160104Orders

Re: load files with specific names from folder through for loop

sunny_talwar — Thu, 12 Jan 2017 19:42:33 GMT

May be try this:

Load Concat(Distinct Chr(39) & Date(%DateKey,'YYYYMMDD') & 'Orders' & Chr(39), ', ') as DateKey

Re: load files with specific names from folder through for loop

sunny_talwar — Thu, 12 Jan 2017 19:43:13 GMT

Should give you this (with single quote around each filename):

'20160101Orders', '20160102Orders', '20160103Orders', '20160104Orders'

Re: load files with specific names from folder through for loop

markgraham123 — Thu, 12 Jan 2017 19:50:41 GMT

Sunny,

Sorry if i'm confusing.

These dates are dynamic and will keep changing.

I would like to load 20160101Orders, 20160102Orders, 20160103Orders, 20160104Orders from a folder without giving static values as these dates keep changing.

Re: load files with specific names from folder through for loop

sunny_talwar — Thu, 12 Jan 2017 19:56:37 GMT

Yes and that's why I am not asking you to hard code, but use this

Load Concat(Distinct Chr(39) & Date(%DateKey,'YYYYMMDD') & 'Orders' & Chr(39), ', ') as DateKey

Here Chr(39) = Single quote and the end result will be that your variable will have these values

'20160101Orders', '20160102Orders', '20160103Orders', '20160104Orders'

Does this make sense?

Re: load files with specific names from folder through for loop

markgraham123 — Thu, 12 Jan 2017 20:01:18 GMT

Sunny,

The issue is with the load filed:

FOR EACH File in $(vFilelist)

Table1:

LOAD *

FROM [$(vFolder)\$(vFilelist).qvd] (qvd);

It is taking Load * from \\Folder\20160101Orders', '20160102Orders', '20160103Orders', '20160104Orders'.

Req; O/P:

Load * from \\Folder\20160101Orders,

Load * from \\Folder\20160102Orders, etc...

Re: load files with specific names from folder through for loop

sunny_talwar — Thu, 12 Jan 2017 20:21:59 GMT

Can you share your logfile or screenshot of the execution window?