Load only specific dates from QVDs

[SilviyaK]

Hi all,

I have a folder containing daily snapshots of data. The format of the name is the following:

DAT_2024-09-12.qvd

I currently have 3 months of data in there and what I want to do is to load all dates available for the current month and only Mondays for the previous months.

Any help with the scripting part is highly appreciated! 

[madelonjansen]

In your load statement, add a new field:

Date#(Subfield(Subfield(Filebasename(), '_', -1), '.', 1), 'YYYY-MM-DD') as Date

[Kushal_Chawda · Accepted solution]

@SilviyaK  try below

AllFiles:
first 1 LOAD date(Date#(SubField(FileBaseName(),'_',2),'YYYY-MM-DD')) as Date,
                     FileBaseName() as FileName
FROM [lib://Folder/Qvd/DAT_*.qvd]
(qvd);

let vCurrentMonthStart = floor(MonthStart(Today()));
let vCurrentMonthEnd = floor(monthend(Today()));
let vPreviousMonthStart = floor(monthstart(addmonths(Today(),-1)));
let vPreviousMonthEnd = Floor(monthend(addmonths(Today(),-1)));

RequiredFiles:
Load FileName
Resident AllFiles
where Date >= $(vPreviousMonthStart) and Date <= $(vPreviousMonthEnd) and WeekDay(Date)='Mon';

Load FileName
Resident AllFiles
where Date >= $(vCurrentMonthStart) and Date <= $(vCurrentMonthEnd);

Drop Table AllFiles;

Data:
Load 0 as temp
AutoGenerate 0;

for i=1 to FieldValueCount('FileName')

let vFileName = FieldValue('FileName',$(i));

Concatenate(Data)
Load *,
         FileBaseName() as FileName
FROM [llib://Folder/Qvd/$(vFileName).qvd]
(qvd);

Next

Drop Table RequiredFiles;

Drop Field temp;