topic Re: Get the File with the Max Date in QlikView

Get the File with the Max Date

Wed, 23 Apr 2014 15:03:28 GMT

Hello!

I have a folder named Selections with many CSV files that have the following format:

Dime_DD_MM_YYYY_HH_MM_SS.csv

For example:

Dime_01_04_2014_17_23_17.csv

Dime_02_04_2014_17_27_05.csv

Dime_03_04_2014_17_15_30.csv

Dime_03_04_2014_19_25_35.csv

What I need is to return the file that has the max date and then load the data into my model.

In this case, the file would be: Dime_03_04_2014_19_25_35.csv.

Do you know how could I do that?

Thank you!!!

Re: Get the File with the Max Date

its_anandrjs — Wed, 23 Apr 2014 15:08:12 GMT

Use filebasename() for this

Re: Get the File with the Max Date

Roop — Wed, 23 Apr 2014 15:14:40 GMT

I would perform:

For Each file in the directory and process the dates from the filenames

Then sort the table according to date

Set a variable as the first record in the table using Peek

Read the table from variable name

Hope this helps

Re: Get the File with the Max Date

bbi_mba_76 — Wed, 23 Apr 2014 15:25:32 GMT

Hi,

see the attached file

Re: Get the File with the Max Date

its_anandrjs — Wed, 23 Apr 2014 15:52:34 GMT

Load your CSV file like the below code and every time the code runs it loads the latest file

Tab1:

LOAD

FileBaseName() as filename,

Timestamp(Timestamp#(Right(FileBaseName(),19),'DD_MM_YYYY_hh_mm_ss'),'DD/MM/YYYY hh:mm:ss') as Date

FROM

Dime_*.csv //Put * here to load all file in folder

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Maxdate:

LOAD

MaxString(filename) as Mdate

Resident Tab1;

LET vMaxFileName = peek('Mdate',-1,'Maxdate');

DROP Table Tab1;

New:

LOAD

FROM

Dime_*.csv

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

Where filename = $(vMaxFileName);

Regards