Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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.
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.