Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikwiz123
Creator II
Creator II

Excel Load

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.

Labels (3)
1 Solution

Accepted Solutions
maxgro
MVP
MVP

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

 

View solution in original post

5 Replies
maxgro
MVP
MVP

 

 

// 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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

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

 

View solution in original post

qlikwiz123
Creator II
Creator II
Author

Perfect. Thank you so much