Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Experts,
I have a dataource as excel the output on the server displays with two Language packages 1. English 2. Chinese
and the excel has an update for every three months and stored back with last updated date on server.
Now my requirement is : Choose last three data updated excel files for only English language and load in my script for analysis.
any suggestions? Thank you in advance
Hi Sagar,
Could you please sample excel file with all the columns inside it ?
Regards,
Mahamed
Hello Mahmmed,
Here is the sample screenshot of the sever folder. Inside I have different listed KPIs for different countries where their product ranking is changed every 3 months.
Dear Sagar,
You have sent the screenshot, I will need the actual excel file.
Hi ,
Can you post your excel file names!
-Hirish
Use logic like:
Let vBaseDate = Date(MonthStart(Today(), -3), 'YYYY-MM');
For Each vFile in FileList('*_English.xlsx')
Let vFileBase = SubField(SubField(vFile, '\', -1), '.', 1);
Let vDate = Date#(Left(vFileBase, 6), 'YYYY-MM');
If vDate >= vBaseDate Then
LOAD *
FROM [$(vFile)]
(ooxml, embedded lables, table is Sheet1);
End If
Next
Adjust the filepath inside FileList to point to the correct location for the files. Adjust the LOAD statement to your preferences.
EDIT - changed date comparison to >=
Hi
may be like this,
LET vFilePath = 'your path\latest Three excel files';
FOR EACH file in FileList('$(vFilePath)\Eng*.xlsx');
LET vFileYear = Left(Right(file, 9), 4);
LET vMaxYear1 = (Rangemax(vFileYear, vMaxYear1))-1;
LET vMaxYear2 = (Rangemax(vFileYear, vMaxYear2))-2;
LET vMaxYear = (Rangemax(vFileYear, vMaxYear));
Next
Data:
LOAD *
FROM
[$(vFilePath)\Eng-$(vMaxYear1).xlsx]
(ooxml, embedded labels, table is Data);
LOAD *
FROM
[$(vFilePath)\Eng-$(vMaxYear2).xlsx]
(ooxml, embedded labels, table is Data);
LOAD *
FROM
[$(vFilePath)\Eng-$(vMaxYear).xlsx]
(ooxml, embedded labels, table is Data);
By this you can pick last three years excel files based on the filename. we can do for filetime too using filetime()Function too.
PFA,
Hope this Helps,
Hirish
Try the Bellow script...
Place all your excel sheets in "D:\Qlik_201268" folder. I am attaching the Excel files and QVW files...
//Read the Filenames only
for each File in filelist('D:\Qlik_201268\*_ENGLISH.XLSX')
FileNames:
Load *inline
[
FileName
$(File)
];
next
//Sort the File names in Descending Order
Temp:
Load FileName,
RecNo()
resident FileNames
order by FileName Desc;
// Loads the Top 3 Files Data only
For i=0 to 2
Let vFileName=peek('FileName',$(i),'Temp');
Final_Data:
Load *
from $(vFileName) (ooxml, embedded labels, table is Sheet1);
next i
drop tables FileNames,Temp;