Daily Data Files - Only Load Last Day Of Each Month

[SonnyDelete]

Dear Qlik Community,

I’m looking for any smart suggestions on how I can overcome this scenario.

I have a directory full of data files which are created on a daily basis (please see attachment) but going back to the beginning of 2018.

I’m looking for a way (in the load script) to only load files reflecting the last day of each month – DailyData.20190630.csv, DailyData.20190731.csv, DailyData.20190831.csv etc.

If anyone’s come up against this or has any suggestions that’d be great.

Many thanks,

J

[rathore01]

hi there,
pls try below code
Fetch file name in table from https://community.qlik.com/t5/QlikView-Scripting/Read-all-file-names-from-folder/td-p/1117427 article

SET vFolder = '\\files'; //Storing the folder path in a variable.
TmpFileLog:
FileName
DailyData.20190630
DailyData.20190731
DailyData.20190831

Temp_Date:
load
monthend(loaddate) as finaldate;
load
date(date#(fileloaddate,'yyyymmdd'),'mm/dd/YYYY') as loaddate;
Load
subfield(FileName,'.',2) as fileloaddate
resident TmpFileLog;
drop table TmpFileLog;

let vDateCount = NoOfRows('Temp_Date');
For i=0 to $(vDateCount) -1
LET vLoadDate=Peek('SnapDate',$(i),'Temp_Date');

Load * FROM $(vFolder)\DailyData.$(vLoadDate).csv;

next;

[rwunderlich · Accepted solution]

How about:

FOR Each file in filelist('lib://data/*.csv')
 Let fdate = num(Date#(SubField('$(file)', '.', -2), 'YYYYMMDD'));
  IF $(fdate) = floor(MonthEnd($(fdate))) THEN
    Data:
    LOAD
      *,
      Date($(fdate)) as FileDate
    FROM [$(file)]
    (txt, codepage is 28599, embedded labels, delimiter is ',', msq);
  ENDIF
Next file

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[rathore01]

Thanks Rob...yes this one is quick and optimized. 🙂

[SonnyDelete]

Thanks for the help Rwunderlich, Rathore01 - much appreciated.