Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Surya
Creator II
Creator II

Dynamically load qvd's from from folder

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

 

Labels (1)
2 Solutions

Accepted Solutions
RsQK
Creator II
Creator II

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;

View solution in original post

Surya
Creator II
Creator II
Author

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

View solution in original post

4 Replies
RsQK
Creator II
Creator II

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
Creator II
Creator II
Author

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

RsQK
Creator II
Creator II

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
Creator II
Creator II
Author

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