topic Re: Selecting file with max date in file name in QlikView

Selecting file with max date in file name

Anonymous — Tue, 29 Apr 2014 18:30:07 GMT

Hi Friends

I am thanking you for your time.

I have multiple xls files as under

GL20131031.XLS

GL20130930.XLS

GL20130430.XLS

How do I select file which has max date in file name(in this case GL20131031.XLS) at script level?

Thanks

Re: Selecting file with max date in file name

MK_QSL — Tue, 29 Apr 2014 18:58:59 GMT

Try below in your script

=======

FileName:

LOAD

FileName() as FileName,

Date(Date#(MID(FileName(),3,8),'YYYYMMDD')) as Date

FROM

*.xlsx

(ooxml, embedded labels, table is Sheet1);

TempMaxDate:

Load

Max(Date) as MaxDate

Resident FileName;

Let vMaxDate = Date(Peek('MaxDate',0,'TempMaxDate'),'YYYYMMDD');

Drop Tables FileName, TempMaxDate;

TableName:

LOAD

FROM

Sales$(vMaxDate).xlsx

(ooxml, embedded labels, table is Sheet1);

=======

Re: Selecting file with max date in file name

MK_QSL — Tue, 29 Apr 2014 19:21:26 GMT

Only Helpful ?

Is there anything missing?

Re: Selecting file with max date in file name

eduardo_sommer — Tue, 29 Apr 2014 19:29:48 GMT

Your script is loading the filenames from a spreadsheet and I think what Satish wanted was to get the filenames from the folder using a filelist

Eduardo

Re: Selecting file with max date in file name

MK_QSL — Tue, 29 Apr 2014 19:41:37 GMT

OK... Got it...

Use as Below

======================

TempFileName:

LOAD

FileName() as FileName,

Date(Date#(MID(FileName(),3,8),'YYYYMMDD')) as Date

FROM

*.xlsx

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Final:

Load

Resident TempFileName

Order By Date Desc;

Drop Table TempFileName;

Let vFileName = Peek('FileName',0,'TempFileName');

==================================

Use vFileName variable as your result...

Re: Selecting file with max date in file name

eduardo_sommer — Tue, 29 Apr 2014 22:01:26 GMT

I think that the script appendend to this message answers your need to load the newest excel file of a folder.

Unfortunetly, I don't know how to paste the text in this message.

Let me know if this is what you was looking for.

Eduardo