2 Replies Latest reply: Feb 17, 2016 2:58 PM by Massimo Grossi RSS

    Loading rows with max value in a field

    Sebastian Haep

      Hi guys,

       

      What I want to do is the follwing:

      I have a folder with qvds which are named like 09-xyz.qvd or 12-xyz.qvd

      The number at the beginning is always the hour of the export, so the greater the number the later the export.

      With the script below I load them all and concatenate them to one table. I need them all in one table for another calculation.

      After the calculation in another table I want to filter the initial table test so that I only have the rows from the latest file which is the max of _Hour.

       

      I tried it with the inner keep load to just keep the rows with max(_Hour) as you can see. Tried it with a small self-written data sample which worked fine. Afterwards I tried it with this for-loop and the resulting big test table (2.7 Mio rows) and without the calculation between. The filter works somehow as it just keeps 346k rows but it still has 9 and 12h rows. Am I doing any mistake here? Shouldn't it just keep the rows with 12 as _Hour because its the max?

       

      Thanks for your help guys!

      Regards,

      Seb

      Set vPath = C:\Test;
      
      
      For each File in FileList('$(vPath)\*.qvd')
      
      test:
      Load
          ArtID,  
        num(Left(FileBaseName(),2)) as _Hour
      From $(File)
      (qvd);
      
      
      Next
      
      
      filter:
      inner keep load
      ArtID,
      Max(_Hour) as _Hour
      resident test
      group by ArticleNumber;
      
      
      
      
      drop table filter;