Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

load the latest (timestamp) file

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.

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: load the latest (timestamp) file

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

3 Replies
mov
Esteemed Contributor III

Re: load the latest (timestamp) file

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

Not applicable

Re: load the latest (timestamp) file

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,

mov
Esteemed Contributor III

Re: load the latest (timestamp) file

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

Community Browser