Script Optimization Help

[mahitham]

Hi Experts,

Can any one please help me on Below issue.

I have 30 qvds in a folder and randomly team will update the new qvds in future also with below name format.

Requirement is I have to load only Max(Date)and Second Max(date)qvds dynamically into Qlik i.e.,

from the above qvds need to load Sales_Detail_20180521 and Sales_Detail_20180518 qvds.

I have tried the below code. Its working fine but in Table section which is highlighted in Red color to extract the DateField I am loading all the qvds which are not required but for MaxDate and SecMaxDate variables.

so reloading time is highly increasing. Is there any way to optimize the script to load the Sales_Detail_20180521 and Sales_Detail_20180518  qvds dynamically without loading all the qvds into Qlik.

Thanks in advance

Table:
LOAD
FileName() as File_Name,
Date(Date#(mid(FileName(),14,8),'YYYYMMDD'),'YYYYMMDD') as DateField
FROM [lib://QVD/Sales_Detail_*.qvd]
(qvd);

Min_Max:
Load
     Date(Max(DateField),'YYYYMMDD')  as MaxDate,
      Date(Max(DateField,2),'YYYYMMDD') as SecMaxDate     //For any second max date qvd
    // Date(Max(DateField)-1,'YYYYMMDD') as SecMaxDate   //For max(Date)-1 qvd
Resident Table;

LET vMaxDate = Peek('MaxDate',0,'Min_Max');
LET vSecMaxDate = Peek('SecMaxDate',0,'Min_Max');
exit Script;

//Drop table Table;

Final:
LOAD
    Product,
    Sales,
    Asofdate
FROM [lib://QVD/Sales_Detail_$(vMaxDate).qvd]
(qvd);

LOAD
    Product,
    Sales,
    Asofdate
FROM [lib://QVD/Sales_Detail_$(vSecMaxDate).qvd]
(qvd);

[PunamWagh · Accepted solution]

You can replace ur Highlighted code with following Code:

(Just check Name field and calculate date filed accordingly)

set vRoot = '[lib://Test Community/]';

FOR Each Ext in 'qvd'

FOR Each File in filelist ('$(vRoot)'&'\*.'&Ext)

Load '$(File)' as Name

autogenerate 1;

next File

next Ext

[jonathandienst]

How about this:

Set vMaxDate = 0;
Set vMaxDate2 = 0;

Set vMaxFile = '';
Set vMaxFile2 = '';

For Each vFile in ('lib://QVD/Sales_Detail_*.qvd')
	Let vTestDate = Num(Date#(TextBetween(vFile, 'QVD/Sales_Detail_', '.qvd')));
	
	If vTestDate > vMaxDate Then
		vMaxDate = vTestDate;
		vMaxFile = vFile;
	Else If vTestDate > vMaxDate2 Then
		vMaxDate2 = vTestDate;
		vMaxFile2 = vFile; 
	End If
Next

Result:
LOAD * 
FROM [$(vMaxFile)] (qvd);

Concatenate(Result)
LOAD * 
FROM [$(vMaxFile2)] (qvd);

(not tested, so there may be typos).