Pick last 7 excel files in qliksense

[Rsaiq]

Hi Experts,

Can any one please help me on below requirement.

In a Folder I have the files like below

Transaction_20210701.xlsx

Transaction_20210702.xlsx

Transaction_20210703.xlsx

'

'

Transaction_20210709.xlsx

 format is (YYYYMMDD)

Daily some files are updating into this folder randomly.

Now loading these files as Transaction_*xlsx

But here need to load only last 7 days files from the folder.

From filename need to extract the date and from maximum date need to load last 7 days files.

I need only below files to be loaded in qliksense :-

Transaction_20210703.xlsx

Transaction_20210704.xlsx

Transaction_20210705.xlsx

Transaction_20210706.xlsx

Transaction_20210707.xlsx

Transaction_20210708.xlsx

Transaction_20210709.xlsx

Each file containing 1 row data as below :

and i need to load only last 7 days files (Highlighted below)

I have used below code in qliksense :

Let vExcel_File_Location='lib://AttachedFiles';

for Each vExcelFile in FileList('$(vExcel_File_Location)\*.xlsx')
Temp_File_List:
load
'$(vExcelFile)' as File_Name_With_Path,
Date(Date#(SubField(SubField('$(vExcelFile)','_',-1),'.',1),'YYYYMMDD')) as Period_FileName
AutoGenerate 1;

next vExcelFile

Temp:
load Date(Max(Period_FileName),'YYYY/MM/DD') as Period Resident Temp_File_List;
Drop Table Temp;

NoConcatenate
Final_File_List:
load * Resident Temp_File_List where Period_FileName >='$(vPeriod)';
Drop Table Temp_File_List;

for vFile=1 to NoOfRows('Final_File_List')

let vFileName=peek('File_Name_With_Path',vFile=1,'Final_File_List');

Transaction:
LOAD
"Order ID",
"Date"
FROM '$(vFileName)'
(ooxml, embedded labels, table is Sheet);

Next vFile

drop Table Final_File_List;

exit Script;

But i am getting 1st and last file data as below :

Anyone please help me in this scenario.and please let me know what is i am missing in the code.

Please find all days wise file in the attachment.

Thanks in advance.

Please help me on this.

Thanks in advance.

[micheledenardi · Accepted solution]

try this:

Let vExcel_File_Location='lib://BaseFolder/3.DEV/_Temp/test/';


for Each vExcelFile in FileList('$(vExcel_File_Location)\*.xlsx')

    Temp_File_List:
    load
        SubField(SubField('$(vExcelFile)','_',-1),'.',1) as Period_FileName
    AutoGenerate 1;

next vExcelFile

Temp:
NoConcatenate
First 7 load 
	Period_FileName
Resident Temp_File_List
	Order by Period_FileName desc;
Drop Table Temp_File_List;

for i=0 to NoOfRows('Temp')-1
	let vFileName='Transaction_'&peek('Period_FileName',i,'Temp');

	Transaction:
    LOAD
        "Order ID",
        "Date"
    FROM $(vExcel_File_Location)$(vFileName).xlsx
    (ooxml, embedded labels, table is Sheet1);

Next
Drop table Temp;
let vFileName=null();
Let vExcel_File_Location=null();