topic Re: How to load latest file to load script? in QlikView

How to load latest file to load script?

jim_chan — Mon, 22 Oct 2018 05:36:12 GMT

Hi guys,

i have file to load into the loading script, but however, i want to load in the LATEST one only. For exmaple:

i have 2 files: ABC_20181015.csv , ABC_20181018.csv

But i only want to load in the latest(ABC_20181018) only. not per today's date, should follow the latest date of the file(ABC_20181018)

How should i write in script??

Load

from ???????

Rgds

Jim

Re: How to load latest file to load script?

jonathandienst — Mon, 22 Oct 2018 05:45:19 GMT

Loop over the files like this:

Let vMaxDate = 0;

Let vFileToLoad = '';

For Each vFile in ('ABC_????????.csv')

Let vFileDate = TextBetween(vFile, 'ABC_', '.csv');

If vFileDate > vMaxDate Then

vFileToLoad = vFile;

vMaxDate = vFileDate;

End If

Next

Data:

LOAD *

FROM [$(vFileToLoad)] (txt, ...... <other file attributes here> ....;

Re: How to load latest file to load script?

jim_chan — Mon, 22 Oct 2018 06:04:02 GMT

Dear Jonathan,

For Each vFile in ('ABC_????????.csv') - just use ? mark?

Re: How to load latest file to load script?

Anil_Babu_Samineni — Mon, 22 Oct 2018 08:08:06 GMT

Try this way?

File_Names:

LOAD

Date(Date#(TextBetween(FileName(),'_','.'),'YYYYMMDD')) as Date_Extract

FROM Path../ABC_*.csv

(txt, utf8, embedded labels, delimiter is ',', msq);

Max_Date:

Load

Max(Date_Extract) AS Max_Day

Resident File_Names;

LET vMax_Day=DATE(PEEK('Max_Day',0,'Max_Date'),'YYYYMMDD');

LOAD *

FROM ABC_$(vMax_Day).csv

(txt, utf8, embedded labels, delimiter is ',', msq);

Re: How to load latest file to load script?

jonathandienst — Mon, 22 Oct 2018 12:03:18 GMT

The ? is a wild card for a single character. So the 8 ? marks is to match the 8 digit date value in the file name.

Re: How to load latest file to load script?

ffan2008 — Sun, 24 May 2020 06:16:42 GMT

Is it possible to load the file names into a table, as such

load_name:
load *, filename() as Filename

From *.xlsx (txt);

and load the last file name.

.....

FROM
[C:\Data\orders-2020-05-21.xlsx]
(ooxml, embedded labels, table is Orders);

How do I insert the last value into the file name?