Loading N Number Of Files

[Anonymous]

I'm looking to load N number of QVDs from a directory. Files are named as Data_YYYY_MM.qvd (for example, Data_2013_06.qvd). I need to first find the file with latest date stamp and then load last three months of data for example.

Any suggestions?

[Anonymous]

First, you have to find out the file dates.  Maybe this:

A:
LOAD DISTINCT
makedate(mid(filename(),6,4), mid(filename(),12,2)) as FileDate
FROM Data_*.qvd (qvd);

B:
SELECT max(FileDate) as MaxFileDate RESIDENT A;
DROP TABLE A;

LET vMaxFileDate = pick('MaxFileDate');
DROP TABLE B;

Now you can load the files you need.  If three:

FOR N= 1 to 3

LET FileName = 'Data_' & date(addmonths($(MaxFileDate), $(N)-1), 'YYYY_MM');

Data:
LOAD
fieldlist
FROM $(FileName).qvd (qvd);

NEXT

Maybe i got some details wrong, but hope you got the approach, and can make it work.

Regards,
Michael

[Anonymous]

Thank you Michael.

I'm trying to resolve an error when I add:

LET vMaxFileDate = pick('MaxFileDate');

to the code. I get script execution failed message.

Haven't got to the looping part yet.

[vivek_niti]

Hi,

u can use the filelist function to get all the names of the qvd's in that folder and then using the peek function get the max 3 values u want.

FOR EACH var in FileList('C:\WorkFiles\Testing\*')

          test:

          LOAD

                     FileTime('$(var)') AS FileTime,

                     '$(var)' AS FileName

          AutoGenerate 1;

NEXT

[Anonymous]

Sorry, my bad.  It is peek(), not pick().

This is what I actually used in a production application.  Not exacrly what you need, but there are similarities.  I neededonly one latest file:

Files_src:

LOAD FileName() as File FROM ...

;

File:

LOAD DISTINCT
  File,
  TextBetween(File, '_', '.') as FileDate          // date wa a part of file name
RESIDENT Files_src
;
DROP TABLE Files_src;

LatestFile:
FIRST 1
LOAD DISTINCT File as LatestFile RESIDENT File
ORDER BY FileDate DESC;
DROP TABLE File;

LET LatestFile = peek('LatestFile');
DROP TABLE LatestFile;