how to load specific qvds from last 6 month data ( need to load only qvd with first working day )

[qliklearnervir]

we have qvd data from September 2019 to Feb 2020.

i want to programmatically load only qvd for first working day of the month from last 6 month and need to do some of sales and save it to in another table in qvw.

Accountdata_09012019.qvd
.
.
.
.
.
.
.Accountdata_02012020.qvd

please suggest .

[petter]

ACCOUNTDATA: LOAD * INLINE [Dummy];

vPathToQVD = 'D:\QVDs';
vToday = Num(Today());
v6monthsAgo = Num(MonthStart(AddMonths(vToday,-6)));

FOR vDay#=v6monthsAgo TO vToday
  vDayText = Date(day#,'MMDDYYYY');
  IF Not(IsNull(FileSize(vPathToQVD & '\Accountdata_' & vDayText & '.qvd'))) THEN
    ACCOUNTDATA:
    CONCATENDATE LOAD 
      *
    FROM
      Accountdata_$(vDayText).qvd (qvd);
  ENDIF
NEXT

DROP FIELD Dummy FROM ACCOUNTDATA;
vPathToQVD=;vToday=;v6monthsAgo=;vDay#=;vDayText=;

[Vegar]

I'll usally use @rwunderlich  librar QlikView Components when looping through files. 

Try the solution below:

$(Must_Include=http://raw.githubusercontent.com/RobWunderlich/Qlikview-Components/master/Qvc_Runtime/Qvc.qvs);
SET vNoOfHistoryMonths = 6;
LET vFileDateFormat = 'DDMMYYYY';
LET vFilePath = 'C:\Users\Vegar\qvd\';

SUB read6months(_file)
	LET _filedate = num(date#(mid('$(_file)', len('$(vFilePath)Accountdata_')+1, 8), '$(vFileDateFormat)'));
	trace $(_filedate);
	IF  ($(_filedate) >=   monthstart(today(), -$(vNoOfHistoryMonths)+1)) AND lastWorkDate(monthstart($(_filedate)),1) = $(_filedate)  THEN
		//Autoconcatenate
		Accountdata:
		LOAD *
		FROM '$(_file)' (qvd);
	ENDIF
ENDSUB //read6months

CALL Qvc.ListFiles('$(vFilePath)', 'accountdata*.qvd', '0', 'read6months');

Call qvc.cleanup

BR
Vegar

[Vegar]

I like your suggested solution @petter , but it looks like it's missing the first business day of the month criteria. It looks like your loop will  loop through all files for the last six months not only the file fromthe first business day of the month.

[petter]

Yes you're most probably right.

I typed it out directly into the browser from the top of my head. I should have pointed out that it is untested and unrun and might be considered more like pseudo-code or a pointer in the right direction...

[Brett_Bleess]

It looks like you have some very good posts from Vegar and Petter, if those did help you, be sure to circle back to the thread to close things out by using the Accept as Solution button on any of the posts that helped you with things.  If you still have further questions, please leave an update with what you still need, otherwise please do close the thread for us.

Regards,
Brett