Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Oldest Excel file LOADING

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

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

6 Replies
Highlighted
Partner
Partner

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

Highlighted

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Contributor III
Contributor III

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

Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

Highlighted
Contributor III
Contributor III

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