topic Re: Load Latest Excel from Folder in QlikView

Excel Load

qlikwiz123 — Sat, 16 Nov 2024 18:57:34 GMT

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.

Re: Load Latest Excel from Folder

maxgro — Wed, 11 Mar 2020 20:28:25 GMT

// 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

Re: Load Latest Excel from Folder

qlikwiz123 — Wed, 11 Mar 2020 20:50:57 GMT

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

Re: Load Latest Excel from Folder

qlikwiz123 — Thu, 12 Mar 2020 13:52:35 GMT

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

Re: Load Latest Excel from Folder

maxgro — Wed, 11 Mar 2020 21:57:12 GMT

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');

Re: Load Latest Excel from Folder

qlikwiz123 — Thu, 12 Mar 2020 13:50:18 GMT

Perfect. Thank you so much