Files: LOAD subfield('$(vFile)','\',-1) as FileName, date(date#(mid('$(vFile)',index('$(vFile)','_',-1)+1,4),'DDMM')) as Month, '$(vFile)' as Path AutoGenerate 1;
next
Files_needed: NoConcatenate first 2 LOAD * Resident Files Order by Month desc;
DROP Table Files;
Data: LOAD * Inline [ Temp ];
for i=1 to FieldValueCount('Month')
let vFilePath = FieldValue('Path',$(i));
Concatenate(Data) LOAD *, FileBaseName() as FileName FROM [$(vFilePath)] (ooxml, embedded labels, table is Sheet1); // you can change this highlighted part as you may have different way of loading the excel files
NEXT
DROP Table Files_needed;
DROP Field Temp;
It is supposed to load the two latest files, I have files named Real_DDMMYYYY. But it is considering that the file Real_31082020 is latest that Real_11092020 which is not true. Can you please help me with that, I have a deadline in two days. Thank you
Hi, just to comment that I was curious of why you said that it's not working, I made 3 excel samples (31082020, 01092020 and 02092020) and it worked, retrieving only the 2 last files. Sample attached
Other than that, if you can rename the files as Marcus said has other benefits, so it's better to keep that way, but remember to include the year in the code, don't take only month and day, or in january you will find that the code is keeping the december files because the month is higher.
Thank you for your response. Yes, the script, as you can see, is takig into consideration the whole date.
And the problem with the old format (DDMMYYYY) is that when I created a variable of the date called DATE, when I do max(DATE) it gives me the 31082020 instead of 11092020. But, with the new format (YYYYMMDD) it worked perfectly. Thank you again for your help.
