topic Re: Pick latest file from Drive in QlikView

Pick latest file from Drive

qlikwiz123 — Sat, 16 Nov 2024 18:17:58 GMT

Hi,

I have files being saved everyday in a folder and the names of the files are date and time stamped as below

2020-07-20 10-51 Data.xlsx

2020-07-21 12-22 Data.xlsx

2020-07-22 14-56 Data.xlsx

2020-07-23 10-09 Data.xlsx

2020-07-24 18-32 Data.xlsx

2020-07-25 17-54 Data.xlsx

The format is YYYY-MM-DD HH-MM

I tried some ways but I think due to this unconventional datetime stamp, it is not working

How do I pick up the latest file every time I run the script?

Re: Pick latest file from Drive

fosuzuki — Tue, 28 Jul 2020 18:58:17 GMT

Hi,

you can use this code to generate a list of the files:

for each vFile in FileList('C:\users\username\documents\*.xlsx')

Files:

LOAD '$(vFile)' AS File,

timestamp(timestamp#(left(SubField('$(vFile)', '\', -1),16), 'YYYY-MM-DD hh-mm')) AS Timestamp

AutoGenerate(1);

Then, work on the table to get the newest file.

Re: Pick latest file from Drive

qlikwiz123 — Tue, 28 Jul 2020 19:25:30 GMT

Hi,

This only gets all the files listed in the Directory under 'File' and timestamps under 'Timestamp' columns. But I need to load only the file with maximum date on it. Also, there are other files in the location and hence I only need to do this for the files that end with 'Data' (Example File name: 2020-07-28 14-33 Data.xls)

Re: Pick latest file from Drive

Kushal_Chawda — Tue, 28 Jul 2020 21:14:38 GMT

try below. Change the folder path to your actual folder location

Data:
first 1 LOAD FileBaseName() as FileName,
timestamp#(trim(purgechar(lower(FileBaseName()),'data')),'YYYY-MM-DD hh-mm') as Time
FROM
[C:\*Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Max:
load FirstSortedValue(FileName,-Time) as Max_Time_FileName
Resident Data;

DROP Table Data;

let vMax_Time_File_Name =Peek('Max_Time_FileName',0,'Max');

Data:
LOAD *
FROM
[C:\$(vMax_Time_File_Name).xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Pick latest file from Drive

qlikwiz123 — Wed, 29 Jul 2020 15:18:17 GMT

Thank you 🙂

Re: Pick latest file from Drive

qlikwiz123 — Thu, 30 Jul 2020 14:34:57 GMT

H @Kushal_Chawda

I don't see it picking up the file at all. And hence the variable is empty and the file reload fails. Here are my files that I am using to test.

Re: Pick latest file from Drive

Kushal_Chawda — Thu, 30 Jul 2020 15:14:28 GMT

Hi,

You have blank excel. Please put some data. I assumed that excel file should have data.

Re: Pick latest file from Drive

qlikwiz123 — Thu, 30 Jul 2020 15:31:02 GMT

Got that, thank you