Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
priyarane
Specialist
Specialist

pick latest file

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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

4 Replies
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein