topic Re: Load only specific dates from QVDs in Qlik Automate

Load only specific dates from QVDs

SilviyaK — Thu, 12 Sep 2024 10:10:39 GMT

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!

Re: Load only specific dates from QVDs

madelonjansen — Thu, 12 Sep 2024 10:18:03 GMT

In your load statement, add a new field:

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

Re: Load only specific dates from QVDs

Kushal_Chawda — Thu, 12 Sep 2024 11:57:54 GMT

@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);

Drop Table RequiredFiles;

Drop Field temp;