Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
jim_chan
Contributor III

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
MVP
MVP

Re: How to load latest file to load script?

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

4 Replies
MVP
MVP

Re: How to load latest file to load script?

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

jim_chan
Contributor III

Re: How to load latest file to load script?

Dear Jonathan,

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

Re: How to load latest file to load script?

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

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

Re: How to load latest file to load script?

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