Oldest Excel file LOADING

[quiquehm]

Hi all,

I have a folder where I keep a bunch of snapshot data in excel files. The way I name the files is : YYYYMMDD_Data.xlsx

How could I tell Qlikview to load only the oldest file from that folder in my script ? ( by oldest I mean just looking at the string YYYYMMDD )

Thanks for any trick to do this

Enrique

[shree909]

Hi

Try this

SalesFolder:   //Here Your getting all the file names that are present in the source folder//

LOAD

    // RecNo() AS Records,

    FILENAME() AS KEY

FROM

[..\DATA\SALESREPORT *]

(ooxml, embedded labels, table is Sales);

MAX_FILE:  // Using resident your loading only the file that has minimum Timestamp//

LOAD

Minstring(KEY)  AS MAX_FILE

RESIDENT SalesFolder;

LET vMaxFile=peek('MAX_FILE','-1','MAX_FILE');  // Storing that (maxfile/min File) in a variable//

DROP TABLE SalesFolder;

SalesFolderWithMaxDate: // loading only the file that has max timestamp from the source folder//

lOAD *

FROM

[..\DATA\$(vMaxFile)]

(ooxml, embedded labels, table is Sales);

/*  U can Create a task by daily or hourly ( On qlikview Server) how your files are updated on the source folder */

Or go through this link :  QlikView should pull automatically from "New Updated Excel Sheet" by latest date.

Let me know if this works

Thanks

[alexandros17]

TempTab:

Load Left(FileBaseName() ,8) as myFile from 'C:\.....\......\*.*';

MinVal:

Load min(myFile) as minimum From TempTab group by myFile;

Let mini = peek('minimum',0,'MinVal');

load now from your file xlsx as follow:

Load * From .... $(mini)_data.xlsx .....

let me know

[rwunderlich]

It looks like  your file naming convention of YYYYMMDD will make the oldest file be first in sort order, so I think the simplest may be:

FOR Each file in FileList('*_data.txt')

  LOAD...

  FROM  [$(file)]

  (biff,.....);

  Exit For     // Exit the loop after loading first file

NEXT file

-Rob

http://masterssummit.com

http://robwunderlich.com

[quiquehm]

Sorry Rob, my mistake, where I said oldest file I should have said newest file. I am interested in loading only the newest file in the folder. By the way on your proposed script, last line where you add EXIT FOR ...would I need to add "1" at the end ? ( so the loop stops after the first file has been loaded )...or it is just enough to leave it like that ( EXIT FOR ).

Thanks a lot for your time and help

Enrique

[rwunderlich · Accepted solution]

When the loop ends $(file) will point to the last (newest) file. So a simple solution would be to let a do-nothing loop run and LOAD $(file) after the loop.

FOR Each file in FileList('*_data.txt')

NEXT file

LOAD...

FROM  [$(file)]

(biff,.....);

-Rob

http://masterssummit.com

http://robwunderlich.com

[quiquehm]

Thank you very much guys for your help. I managed to follow your suggestions ( and the quickest way provided by Rob ). I added some calculated fields , derived from the file name to have my datestamp in the resulting table as well :

Script :

FOR Each file in FileList( '*_Activ_EU.xlsx' )

NEXT file

LOAD

left(Filename(),8)                                                                      as DateStamp_string,

Date(date#(left(Filename(),8),'YYYYMMDD'), 'DD-MMM-YY')    as DS_Date,

Year(date#(left(Filename(),8),'YYYYMMDD'))                           as DS_Year,

Month(date#(left(Filename(),8),'YYYYMMDD'))                         as DS_Month,

dual('Q0'&ceil(month(date#(left(Filename(),8),'YYYYMMDD'))/3),ceil(month(date#(left(Filename(),8),'YYYYMMDD'))/3))     as DS_QTR

FROM [$(file)]

(ooxml, embedded labels, table is Sheet1);

Do you know any shortcut for above scenario where you need to add several calculated fields basically using the same original field, in this case being  left(Filename(),8) ? I thought of maybe just loading this calculated field first ( alone ) ...then build the other fields from this one ( already aliased as DateStamp_string ) using a resident load ? Any other ( better, faster ) ideas ?

Thanks again for your time and help

Enrique