topic Re: Loading N Number Of Files in QlikView

Loading N Number Of Files

Anonymous — Mon, 17 Jun 2013 21:53:22 GMT

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?

Re: Loading N Number Of Files

Anonymous — Tue, 18 Jun 2013 01:26:59 GMT

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

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

Regards,
Michael

Re: Loading N Number Of Files

Anonymous — Fri, 21 Jun 2013 21:44:16 GMT

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.

Re: Loading N Number Of Files

vivek_niti — Sat, 22 Jun 2013 02:06:20 GMT

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;

Re: Loading N Number Of Files

Anonymous — Mon, 24 Jun 2013 12:23:09 GMT

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;