Loading rows with max value in a field

[#1]

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;

[marcus_sommer]

Your filter contained a further field ArtID which probably caused that you get several max. values.

- Marcus

[maxgro · Accepted solution]

if you want to filter by max of ArtID

inner keep load 

ArtID, 

Max(_Hour) as _Hour 

resident test 

group by ArtID;

if you only want the max of all records

inner keep load 

Max(_Hour) as _Hour 

resident test ;