topic Re: pick latest file in QlikView

pick latest file

priyarane — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: pick latest file

bindu_apte — Wed, 28 Sep 2016 05:29:18 GMT

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

Re: pick latest file

avinashelite — Wed, 28 Sep 2016 05:42:26 GMT

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

Re: pick latest file

jagan — Wed, 28 Sep 2016 05:53:32 GMT

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.

Re: pick latest file

jonathandienst — Wed, 28 Sep 2016 06:12:14 GMT

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.