Pick last three week QVD

[New-Qlik]

Hello

i have to 2 months  QVDS stores in a location and i want to load last three week qvd data in appllication where qvd name is like

profit_20191010.qvd

profit_20191011.qvd etc and one week have 5 days so week day chekc is 5(monday to friday ).

Thanks 

AT

[vunguyenq89 · Accepted solution]

Hi,

According to the problem description, I assume that:

• You have 1 qvd file for every weekday including weekends, but only want to load qvd files generated in workdays (mon-fri)
• You want to load the latest 15 working days (3 weeks) from today. For example if today is Mon 11 Nov 19, you want to load from Tue 22 Oct 19

If the above assumptions are correct, then the following script should do the trick:

//Load today qvd
Profit:
LOAD * FROM [lib://Data/profit_20191111.qvd] (qvd);

//Load 3 weeks of data
Set vNumWeeks = 3;
For i = 1 to (vNumWeeks*7-1)
	vDate = Num(Today() - i);
    vWeekDay = Num(WeekDay(vDate));
    If vWeekDay < 5 Then
    	vFileName = 'profit_' & Date($(vDate),'YYYYMMDD') & '.qvd';
        Concatenate(Profit) LOAD * FROM [lib://Data/$(vFileName)] (qvd);
    End If;
Next i;

Hope it helps!

BR, 

Vu Nguyen

[New-Qlik]

Hello Vu Nguyen ,

Thanks for replying . i got me right i have separate qvd for daily profit figures . and i wnat to load last three full week data 

Profit:
LOAD * FROM [lib://Data/profit_20191111.qvd] (qvd);

can u automate this line meaning not hard coding  date as 'profit_ '

[vunguyenq89]

Hi,

If you always have today in your set of QVD files, you can automate the first load with

//Load today qvd
vTodayQVD = Date(Today(),'YYYYMMDD');
Profit:
LOAD * FROM [lib://Data/profit_$(vTodayQVD).qvd] (qvd);

BR,

Vu Nguyen