Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Anonymous
Not applicable
Author

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

View solution in original post

3 Replies
Anonymous
Not applicable
Author

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
Author

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,

Anonymous
Not applicable
Author

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