topic Re: Dynamically load qvd's from from folder in App Development

Dynamically load qvd's from from folder

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

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

Re: Dynamically load qvd's from from folder

RsQK — Wed, 25 Aug 2021 10:11:29 GMT

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?

Re: Dynamically load qvd's from from folder

Surya — Wed, 25 Aug 2021 10:19:14 GMT

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

Re: Dynamically load qvd's from from folder

RsQK — Wed, 25 Aug 2021 10:43:06 GMT

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;

Re: Dynamically load qvd's from from folder

Surya — Wed, 25 Aug 2021 11:32:46 GMT

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