Loading rows with max value in a field

Wed, 17 Feb 2016 17:56:42 GMT

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;

Re: Loading rows with max value in a field

marcus_sommer — Wed, 17 Feb 2016 19:28:48 GMT

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

- Marcus

Re: Loading rows with max value in a field

maxgro — Wed, 17 Feb 2016 19:58:27 GMT

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 ;

topic Re: Loading rows with max value in a field in QlikView

Loading rows with max value in a field

Re: Loading rows with max value in a field

Re: Loading rows with max value in a field