topic Re: Re: Oldest Excel file LOADING in QlikView

Oldest Excel file LOADING

quiquehm — Wed, 12 Nov 2014 17:06:53 GMT

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

Re: Oldest Excel file LOADING

shree909 — Wed, 12 Nov 2014 17:17:31 GMT

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

alexandros17 — Wed, 12 Nov 2014 17:24:50 GMT

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

Re: Oldest Excel file LOADING

rwunderlich — Wed, 12 Nov 2014 17:33:21 GMT

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

Re: Oldest Excel file LOADING

quiquehm — Thu, 13 Nov 2014 21:28:28 GMT

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

Re: Re: Oldest Excel file LOADING

rwunderlich — Thu, 13 Nov 2014 22:07:51 GMT

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

Re: Oldest Excel file LOADING

quiquehm — Mon, 17 Nov 2014 17:37:51 GMT

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