9 Replies Latest reply: Nov 1, 2013 7:47 AM by Onur Civelek RSS

    LOAD Max(DateFile)

      Hi together,

       

      according to this script:

       

      TEMP_PICKER_QMCLog:
          LOAD Distinct FilePath() AS FileName, FileTime() AS DateFile
          FROM D:\Qlikview11\DistributionService\1\Log\Root_201*.txt;
         
          INNER JOIN (TEMP_PICKER_QMCLog) LOAD max(DateFile) as DateFile RESIDENT TEMP_PICKER_QMCLog;
          LET PointerToQMCLogfile = peek('FileName',-1);
          //DROP TABLE TEMP_PICKER_QMCLog;
      
      QMCLogfile:
      LOAD @1,
           @3,
           @4
      FROM '$(PointerToQMCLogfile)' (txt, utf8, no labels, delimiter is ',', msq);
      
      
      

       

       

      i am loading the latest file in qlikview.  Nowadays the day before should also additionally be loaded in Qlikview.

       

      I tried it with  max(DateFile,2). But unfortunately, only one file will be loaded. How do I implement it so that only the last two days will be loaded ?

       

      Thank you for your advice.

       

      Best Regards,

        • Re: LOAD Max(DateFile)
          Gysbert Wassenaar

          Perhaps something like this:

           

          TEMP_PICKER_QMCLog:
          LOAD Distinct FilePath() AS FileName, FileTime() AS DateFile  
          FROM D:\Qlikview11\DistributionService\1\Log\Root_201*.txt;  
          
          
          Temp2:
          First 2
          load * resident TEMP_PICKER_QMCLog
          order by DateFile desc;
          
          let vLatestFile = peek('FileName',0,'Temp2');
          let vSecondLatestFile = peek('FileName',1,'Temp2');
          
          LOAD * from '$(vLatestFile)' (txt, utf8, no labels, delimiter is ',', msq); 
          
          LOAD * from '$(vSecondLatestFile)' (txt, utf8, no labels, delimiter is ',', msq); 
          
            • Re: LOAD Max(DateFile)

              Hi Gysbert,

               

              your adviced script loads regrettably all Files !

               

               

              I need only the last 2 days listet. Actually today (30.10.2013) and yesterday(29.10.2013)

               

              Thank you

              Best Regards,

                • Re: LOAD Max(DateFile)
                  Stefan Wühl

                  Onur Civelek wrote:

                   

                  Hi Gysbert,

                   

                  your adviced script loads regrettably all Files !

                   

                   

                  I need only the last 2 days listet. Actually today (30.10.2013) and yesterday(29.10.2013)

                   

                  Could you double check this? I doubt that all files are loaded when using Gysbert's script snippet.

                  Your DateFile values will still show all available file dates, because you haven't inner joined nor dropped this field.

                   

                  Could you check which files are loaded e.g. by posting the document log?

              • Re: LOAD Max(DateFile)
                Andrew Pettit

                If you truly run reloads daily and the max() works why not do max()-1 for the previous day?

                • Re: LOAD Max(DateFile)

                  Hello again,


                  maybe my script is not suitable for my project.


                  The QMC generates the following logs. I want to load only the last 2 days. In this case today and yesterday:


                  Root_20131030.txt 

                  Root_20131029.txt


                   

                  Is there a more appropriate way or an example script to do this ?


                  Thank you very much !

                  • Re: LOAD Max(DateFile)

                    Hi togehter,

                     

                    i got a solution from Clever. Many thanks to him.


                    SET PATH='D:\Qlikview11\DistributionService\1\Log';  
                    LET TwoDays=date(today()-2,'YYYYMMDD');   
                      
                    for each file in FileList('$(PATH)\Root_*.txt')  
                      let filebasename =subfield('$(file)','\',-1);  
                      if '$(filebasename)' follows 'Root_$(TwoDays).txt' then  
                      QMCLog:  
                      LOAD 
                      @1 AS QMCLogDate, 
                      @3 AS QMCLogType, 
                      @5 AS QMCLogTask, 
                      @6 AS QMCLogApp, 
                      @7 AS QMCLogInfo1, 
                      @8 AS QMCLogInfo2
                       
                    FROM
                    [D:\Qlikview11\DistributionService\1\Log\Root_20131101.txt]
                    (txt, utf8, no labels, delimiter is spaces, msq, no eof);
                      end if  
                    next
                    


                     

                    This script load only the last two days and works perfectly.