3 Replies Latest reply: Apr 15, 2013 12:58 PM by Michael Solomovich RSS

    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.

        • Re: load the latest (timestamp) file
          Michael Solomovich

          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

              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,