Excel Load

[qlikwiz123]

I have a Server folder with Excels in the folder being added every month.

File Names:

January19

February19

March19

.

.

December19

January20

February20

I need to load only the latest Excel which is February20 in this case. I tried all the solutions available on the threads but nothing seems to be working with my filename format. Please advise.

[maxgro]

// add the MY field

FileNames:
load *, Date(Date#(Left(MonthYear, Len(MonthYear)-2) &'-'& Right(MonthYear,2) & '-01', 'MMM-YY-DD')) as MY;
load * inline [
MonthYear
January19
February19
March19
December19
January20
February20
];

// resident  load 1 record order by date desc (latest excel file)

LastFileName:
First 1 load MonthYear as ExcelToLoad
Resident FileNames
order by MY desc;

// set the variable with the name of the excel file to load 

LET vExcelToLoad = Peek('ExcelToLoad');

// .... load the file

[qlikwiz123]

Hi,

Looks like this requires me to create Inline table with all the file names. It becomes difficult when I have 3-4 years worth of excels and pick up the latest. It should be dynamic, something using FileList perhaps? How do we do it

[qlikwiz123]

Used Filebase()

FileNames:
load *, Date(Date#(Left(MonthYear, Len(MonthYear)-2) &'-'& Right(MonthYear,2) & '-01', 'MMM-YY-DD')) as MY;
Load filebasename() as MonthYear

FROM [E:/downloads/*xlsx;

// resident load 1 record order by date desc (latest excel file)

LastFileName:
First 1 load MonthYear as ExcelToLoad
Resident FileNames
order by MY desc;

// set the variable with the name of the excel file to load

LET vExcelToLoad = Peek('ExcelToLoad');

// .... load the file

But loads Apr19 although I have Dec19 as the latest

[maxgro · Accepted solution]

I used an inline load as an example.

With FileBaseName():

tmpFileNames:
FIRST 1 LOAD 
// SubField because I only want the month and year of the file (Apr19 Dec19)
SubField(FileBaseName(),'_',3) as MonthYear         
// change with your path
FROM [E:/downloads/HRC_C_*.xlsx]
(ooxml, embedded labels, table is Foglio1);

FileNames:
load *, Date(Date#(Left(MonthYear, Len(MonthYear)-2) &'-'& Right(MonthYear,2) & '-01', 'MMMM-YY-DD')) as MY
Resident tmpFileNames;

Drop Table tmpFileNames;

LastFileName:
First 1 load MonthYear as ExcelToLoad
Resident FileNames
order by MY desc;   // order by works if MY is a date!

LET vExcelToLoad = Peek('ExcelToLoad');

[qlikwiz123]

Perfect. Thank you so much