Dynamically load QVD's from Folder

Surya — Tue, 30 Nov 2021 16:25:32 GMT

Hello,

I have a folder and we store qvd's in that folder now for one report purpose we need to load only

every month 1st date qvd and current month all qvd's

like A_20210601

A_20210701

A_20210801

A_20210802

A_20210803

up to current date A_20210826

Re: Dynamically load QVD's from Folder

saniyask — Thu, 26 Aug 2021 04:43:43 GMT

Hi,

Can you try this

Date:

LOAD

(Date(Date#(SubField(FileBaseName(),'_',2),'YYYYMMDD'),'YYYYMMDD')) as FileDate

FROM [lib://Data/test/A_*.QVD] (qvd);

NoConcatenate

Load

Date(if(MonthStart(FileDate)<>MonthStart(Today()),MonthStart(FileDate)),'YYYYMMDD') AS FileMonthstart,

if(Month(FileDate)=Month(Today()),FileDate) as CurrentMonthFiles

Resident Date;

Drop table Date;

FOR i = 1 to FieldValueCount('FileMonthstart')

LET vFileName = FieldValue('FileMonthstart', $(i));

TRACE $(FileMonthstart);

FinalTable: // This table will give you the files for every months 1st date excluding current month

LOAD *

From [lib://Data/test/A_$(vFileName).qvd](qvd);

FOR k = 1 to FieldValueCount('CurrentMonthFiles')

LET vFileName = FieldValue('CurrentMonthFiles', $(k));

TRACE $(CurrentMonthFiles);

FinalTable: // This will give you all the files for current month

LOAD * as Test

From [lib://Data/test/A_$(vFileName).qvd](qvd);

Hope this helps .

Saniya