Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

How to get the Max Date Modified in a folder

Is there a way to get the Max Date Modified in a folder.  I have the following folder location:

C:\Users\dyoung\Documents\Qlik\Sense\Apps\F_Gates\backups\

In that folder I have a bunch of back files that look like this:

Name                                                                      Date Modified

backup_F_Gates_160214_0006.dist                          2/14/2016

backup_F_Gates_160212_0008.dist                          5/15/2016

backup_F_Gates_160233_0010.dist                          4/20/2016

backup_F_Gates_160244_0011.dist                          3/13/2016

I would want to only return:

backup_F_Gates_160212_0008.dist                          5/15/2016


Is this possible?


Thanks,

David

6 Replies
Not applicable

Re: How to get the Max Date Modified in a folder

Hey there,

You could use filetime() function. So in your case

Load *, filetime() as Time

From backup_F_Gates_*.dist ;

You can give it a whirl.

Thanks

MVP
MVP

Re: How to get the Max Date Modified in a folder

Tmp:

Load filetime() as FileTime, FileName() as FileName

From backup*.dist ;

Tmp2:

NoConcatenate First 1 LOAD * Resident Tmp Order by FileTime desc;

DROP Table Tmp;

LET vFile = Peek('FileName');

TRACE $(vFile);

shraddha_g
Honored Contributor III

Re: How to get the Max Date Modified in a folder

Try below script:

It might help.

set vPath = 'Connectionname';

for each File in filelist ('ConnectionName\backup_F_Gates_*.dist')

fileTable:

Load '$(File)' as Name,
  num(FileTime('$(File)')) as FileTime
  autogenerate 1;
next File

Modified_Time:

Load max(Filetime) as ModifiedTime

Resident fileTable;

let vMaxTime = num(peek('ModifiedTime',0,'Modified_Time'));


if len(TableNumber('fileTable')) >= 1 then


FinalTMP:

LOAd
SubField(Name,'\',-1)as File,
rowno() as Recno

RESIDENT fileTable
WHERE FileTime > '$(vMaxTime)'+0.001
ORDER BY FileTime desc;

Drop table fileTable;


Let VMxRows = NoOfRows('FinalTMP');

if  $(VMxRows) >0 then

for i=0 to $(VMxRows)-1


let FileRead = peek('File',$(i),'FinalTMP');


Data:
Load *,
FileName() as Filename,
FileTime() as FileTime

From $(vPath)\$(FileRead);


Next

End if

Drop table FinalTMP;

Not applicable

Re: How to get the Max Date Modified in a folder

I understand what you are doing but I am getting a "No qualified path for file: message

Thanks,

David

shraddha_g
Honored Contributor III

Re: How to get the Max Date Modified in a folder

Have you created a folder connection for folder containing all you .dist files?

Not applicable

Re: How to get the Max Date Modified in a folder

Here is the code I used.  I created a folder connection to Backups. 

set vPath = 'Backups';

for each File in filelist ('Backups\backup_F_Gates_*.dist')

fileTable:

Load '$(File)' as Name,

  num(FileTime('$(File)')) as FileTime

  autogenerate 1;

next File

Modified_Time:

Load max(Filetime) as ModifiedTime

Resident fileTable;

let vMaxTime = num(peek('ModifiedTime',0,'Modified_Time'));

if len(TableNumber('fileTable')) >= 1 then

FinalTMP:

LOAd

SubField(Name,'\',-1)as File,

rowno() as Recno

RESIDENT fileTable

WHERE FileTime > '$(vMaxTime)'+0.001

ORDER BY FileTime desc;

Drop table fileTable;

Let VMxRows = NoOfRows('FinalTMP');

if  $(VMxRows) >0 then

for i=0 to $(VMxRows)-1

let FileRead = peek('File',$(i),'FinalTMP');

Data:

Load *,

FileName() as Filename,

FileTime() as FileTime

From $(vPath)\$(FileRead);

Next

End if

Drop table FinalTMP;

Community Browser