topic Re: load the latest (timestamp) file in QlikView

load the latest (timestamp) file

Mon, 15 Apr 2013 13:55:27 GMT

Hi,

i have a question an need some little help

I could work well with this load script a long time.

DataSelection:

LOAD Distinct FilePath() AS FileName,

FileTime() AS DateFile

FROM D:\Qlikview11\Tools\APITool_v3_Qlikview\reports\28781_20*.csv; //Anomalie ?!

LET FileNameOfSites = peek('FileName',-1);

LoadingFile:

LOAD

.........

....

FROM '$(FileNameOfSites)' (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

This snippet of code could recognize the latest file based on the filename and select it for load. Unfortunately, the name of the generated sourcefiles are changed

an the above script does no longer works.

Now i want to select the files based on timestamp instead of the filename.

Can you adapt my loadscript that only the latest file (timestamp) is selected?

Thank you for help.

Re: load the latest (timestamp) file

Anonymous — Mon, 15 Apr 2013 14:22:36 GMT

I think the easiest way is to add this line before the LET statement:

INNER JOIN (DataSelection) LOAD max(DateFile) as DateFile RESIDENT DataSelection;

It will reduce the DataSelection table to only one record (assuming there are no files with the same timestamp), so there is no need to change anything else.

Regards,

Michael

Re: load the latest (timestamp) file

Mon, 15 Apr 2013 16:33:16 GMT

HI Michael,

thank you for your advice. I build it in my code. It seems to work fine. But can I write it below or Is this all right?

TEMP_PICKER_SITES:

LOAD Distinct FilePath() AS FileName,

FileTime() AS DateFile

FROM D:\Qlikview11\Tools\APITool_v3_Qlikview\reports\28781_20*.csv;

INNER JOIN (TEMP_PICKER_SITES) LOAD max(DateFile) as DateFile RESIDENT TEMP_PICKER_SITES;

LET FileNameOfSites = peek('FileName',-1);

DROP TABLE TEMP_PICKER_SITES;

TEMP_PICKER_CAMPAIGN:

LOAD Distinct FilePath() AS FileName,

FileTime() AS DateFile

FROM D:\Qlikview11\Tools\APITool_v3_Qlikview\reports\28779*.csv;

INNER JOIN (TEMP_PICKER_CAMPAIGN) LOAD max(DateFile) as DateFile RESIDENT TEMP_PICKER_CAMPAIGN;

LET FileNameOfCampaign = peek('FileName',-1);

DROP TABLE TEMP_PICKER_CAMPAIGN;

TEMP_PICKER_CAMPAIGNEXT:

LOAD Distinct FilePath() AS FileName,

FileTime() AS DateFile

FROM D:\Qlikview11\Tools\APITool_v3_Qlikview\reports\111573_201*.csv;

INNER JOIN (TEMP_PICKER_CAMPAIGNEXT) LOAD max(DateFile) as DateFile RESIDENT TEMP_PICKER_CAMPAIGNEXT;

LET FileNameOfCampaignExt = peek('FileName',-1);

DROP TABLE TEMP_PICKER_CAMPAIGNEXT;

Thanks a million.

best regards,

Re: load the latest (timestamp) file

Anonymous — Mon, 15 Apr 2013 16:58:03 GMT

Looks reasonable. You don't need the second parameter anymore in the pick() function because there is only one record in the table, but if you leave as is it will work anyway.

Regards,

Michael