Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
SonnyDelete
Contributor
Contributor

Daily Data Files - Only Load Last Day Of Each Month

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

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

4 Replies
rathore01
Partner - Contributor III
Partner - Contributor III

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Partner - Contributor III
Partner - Contributor III

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

SonnyDelete
Contributor
Contributor
Author

Thanks for the help Rwunderlich, Rathore01 - much appreciated.