load files with specific names from folder through for loop

[markgraham123]

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

NEXT

The above is not working.

Any help is highly appreciated.

[markgraham123]

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

[markgraham123]

list

[sunny_talwar]

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

LET vFilelist = peek('DateKey');

[markgraham123]

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

Resident Table1;

o/p of vFilelist in my report:

20160101Orders,20160102Orders,20160103Orders,20160104Orders

[sunny_talwar]

May be try this:

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

[sunny_talwar]

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

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

[markgraham123]

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.

[sunny_talwar]

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?

[markgraham123]

Sunny,

The issue is with the load filed:

FOR EACH File in $(vFilelist)

      Table1:

      LOAD *

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

NEXT

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

Req; O/P:

Load * from \\Folder\20160101Orders,

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

[sunny_talwar]

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