Dynamically load qvd's from from folder

[Surya]

Hello I have a folder

That contain

A_20210825

A_20210824

A_20210823

Like everyday I need to pick only last 2 days qvd's

[RsQK]

Hey, this would work, if you definitely have those qvd's there:

data:
LOAD 0 AS temp_field AUTOGENERATE 0;

LET vToday = DATE(TODAY(),'YYYYMMDD');
LET vYesterday = DATE(TODAY()-1,'YYYYMMDD');

CONCATENATE (data)
LOAD * FROM [$(YourQVDFolderPath)/A_$(vToday).qvd] (QVD);

CONCATENATE (data)
LOAD * FROM [$(YourQVDFolderPath)/A_$(vYesterday).qvd] (QVD);

DROP FIELD temp_field;

If theres no QVD for today, should you load max date qvd and previous one from that?

[Surya]

sorry.. Actually we require last 7 days files and dates also different means may be earlier dates.

[RsQK · Accepted solution]

OK, this then:

SET vDays = 7;

SET YourQVDFolderPath = 'lib://DATA';

temp_filelist:
LOAD
FILENAME() AS filename
FROM [$(YourQVDFolderPath)/*.QVD] (QVD);

temp_filelist2:
LOAD *,
NUM(DATE#(RIGHT(SUBFIELD(filename,'.',1),8),'YYYYMMDD')) as date
RESIDENT temp_filelist;

DROP TABLE temp_filelist;

temp_filelist3:
LOAD *,
ROWNO() AS id
RESIDENT temp_filelist2
ORDER BY date desc;

DROP TABLE temp_filelist2;

INNER JOIN (temp_filelist3)
LOAD
ROWNO() AS id
AUTOGENERATE $(vDays);

data:
LOAD 0 AS temp_field AUTOGENERATE 0;

FOR EACH vQVDFile IN FIELDVALUELIST('filename')

CONCATENATE (data)
LOAD * FROM [$(YourQVDFolderPath)/$(vQVDFile)] (QVD);

NEXT vQVDFile;

DROP FIELD temp_field;
DROP TABLE temp_filelist3;

[Surya · Accepted solution]

@RsQK  Thank you .

Its working

SET vDays = 7;

SET Path = 'lib://JP_CDH_Dealer_GPM_D/50_Data/Test/';

temp_filelist:
LOAD
FILENAME() AS filename
FROM [lib://JP_CDH_Dealer_GPM_D/50_Data/Test/txn_data_*.xlsx](ooxml, embedded labels, table is Tabelle1);

temp_filelist2:
LOAD *,
NUM(DATE#(RIGHT(SUBFIELD(filename,'.',1),8),'YYYYMMDD')) as date
RESIDENT temp_filelist;

DROP TABLE temp_filelist;

temp_filelist3:
LOAD *,
AUTONUMBER(date) AS id
RESIDENT temp_filelist2
ORDER BY date DESC;

DROP TABLE temp_filelist2;

INNER JOIN (temp_filelist3)

LOAD
ROWNO() AS id
AUTOGENERATE $(vDays);

data:
LOAD 0 AS temp_field AUTOGENERATE 0;

FOR EACH vFile IN FIELDVALUELIST('filename')

CONCATENATE (data)
LOAD * FROM [$(Path)/$(vFile)](ooxml, embedded labels, table is Tabelle1);

NEXT vFile;

DROP FIELD temp_field;
DROP TABLE temp_filelist3;