Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

quiquehm
New 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
MVP & Luminary
MVP & Luminary

Re: Re: Oldest Excel file LOADING

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

6 Replies
shree909
Valued Contributor II

Re: Oldest Excel file LOADING

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

Re: Oldest Excel file LOADING

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

MVP & Luminary
MVP & Luminary

Re: Oldest Excel file LOADING

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
New Contributor III

Re: Oldest Excel file LOADING

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

MVP & Luminary
MVP & Luminary

Re: Re: Oldest Excel file LOADING

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
New Contributor III

Re: Oldest Excel file LOADING

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

Community Browser