Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Loading rows with max value in a field

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;

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Loading rows with max value in a field

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 ;

2 Replies

Re: Loading rows with max value in a field

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

- Marcus

MVP
MVP

Re: Loading rows with max value in a field

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 ;

Community Browser