only reload files with certain dates

nicolai_moller — Fri, 05 Oct 2018 11:11:01 GMT

I receive a .csv file once a week and all files received so far are stored in the same folder.

The first file received in each month will be loaded into QlikView and so far I have made a script that

creates a field that identifies all the filenames that have the earliest date in each month.

ex.

File_20170524

File_20170607

File_20180913

I need to create a script that only reloads the first file each month and all the other files should not be reloaded as

it would have to reload over 100 files (>100 MB each).

Anyway this i possible?

Thanks.

Re: only reload files with certain dates

felipedl — Fri, 05 Oct 2018 11:48:06 GMT

Hi Nicolai,

The following code, using on of rwunderlich‌ DoDir script might do the job:

// Includes the script to load all file names
$(must_include=C.2.DoDir.qvs);

// calls the sub for getting all files
call DoDir('File_*');

// transforms some of the data to get file name and date
Files:
Load *,
Num(Date#(subfield(subfield([File Name],'FILE_',2),'.'&DoDirFileExtension,1),'YYYYMMDD')) as [File Date];
Load *,
Upper(Subfield(SubField(FullyQualifiedName,'\',SubStringCount(FullyQualifiedName,'\')+1),DoDirFileExtension,1)) as [File Name]
Resident DoDirFileList;

DROP TABLE DoDirFileList;

// gets max date for all files
MaxField:
Load max([File Date]) as MaxFileDate
Resident Files;

let vMaxFileDate = peek('MaxFileDate',0,'MaxField');

// checks the latest file to be loaded
NoConcatenate
tmpFile:
Load *
Resident Files
where [File Date] = $(vMaxFileDate);

let vFileName = peek('File Name',0,'tmpFile');

// loads the max dated file
LOAD *
FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

drop tables tmpFile,MaxField;

Attached all the files I used.

Hope it helps,

Felipe.

topic Re: only reload files with certain dates in QlikView

only reload files with certain dates

Re: only reload files with certain dates