pick latest file

[priyarane]

Hi Community,

can we pick latest file available in the path.

for EX:

C:\Doc\Text_release20_sep.xlx          ------- created time 15th sep

C:\Doc\Text_release26_sep.xlx          ------- created time 21st sep

C:\Doc\Text_release29_sep.xlx          ------- created time 27th sep

so from above file our qvw should pick last one(created time 27th sep). I have tried with couple of loops but still not getting it. can some one to get loop or any other approach.

-Priya

[bindu_apte]

Hi,

Hope this threads will help you.

https://community.qlik.com/message/936984?&_ga=1.75722069.1945849215.1470303139#936984

Pick the latest excel file from folders | Qlik Community

[avinashelite]

check this

Load the latest file of a folder | Qlik Community

Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

[jagan · Accepted solution]

Hi Priya,

Try like this

let vFilePath = 'C:\Doc';

searchStr = '\*.xlsx'; 

rootPath = '$(vFilePath)';

sub DoDir (Root)

  for each File in filelist (Root&searchStr)

  TmpFileLog:

  LOAD

  *,

  Date#(Mid(FileName, 13, 6), 'DD_MMM')  AS FileDate;

  Load '$(File)' as PathName,

  '$(Root)' as Folder,

  mid('$(File)',index('$(File)','\',-1)+1,len('$(File)')-index('$(File)','\',-1)) as FileName,

  right('$(File)',len('$(File)')-index('$(File)','.',-1)+1) as FileExt,

  Filesize('$(File)') as Size,

  Filetime('$(File)') as FileTime

  autogenerate 1;

  next File

end sub

call DoDir (rootPath)

MaxDate_Temp:

LOAD

Max(FileDate) AS MaxDate

RESIDENT TmpFileLog;

DROP TABLE TmpFileLog;

LET vMaxDate = Date(Peek('MaxDate'), 'DD_MMM');

DROP TABLE MaxDate_Temp;

Data:

LOAD *,

FileName() AS FileName

FROM

[$(vFilePath)\Text_release$(vMaxDate).xlsx]

(ooxml, no labels, table is Sheet1);

Regards,

jagan.

[jonathandienst]

Let vMax = 0;

For Each vFile in FileList('C:\Doc\Text_release*.xlx')

    Let vCurr = FileTime(vFile);

    If vCurr > vMax Then

        Let vMax = vCurr;

        Let vLatest = vFile;

    End If

Next

Data:

LOAD *,

  FileBaseName() as Source

FROM [$(vLatest)]

(biff, ...)

;

Adjust the load statement to your requirements.