Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jim_chan
Specialist
Specialist

How to load latest file to load script?

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

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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> ....;

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

View solution in original post

5 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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> ....;

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

Dear Jonathan,

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

Anil_Babu_Samineni

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);

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jonathandienst
Partner - Champion III
Partner - Champion III

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.

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

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

 

Spoiler

load_name:
load *, filename() as Filename

From *.xlsx (txt);

 

and load the last file name.

 

Spoiler

.....

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?