Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Group By one column only - max date

Hi Qlik Communty:

So, I have this Table1:

TABLE1:
ORDERX_FIELDDATE
10001220/08/2015
10001415/08/2015
10002419/08/2015
10002420/08/2015
10002221/08/2015
10003418/08/2015
10003220/08/2015

and need to Group By ORDER showing the max(DATE) and X_FIELD, something like this:

 

ORDERX_FIELDDATE
10001220/08/2015
10002221/08/2015
10003220/08/2015

If I write this:

Table2:

LOAD ORDER, X_FIELD, MAX(DATE) AS DATE

RESIDENT TABLE1 GROUP BY ORDER

I got an error because X_FIELD is missing after ORDER in Group By.

If I write GROUP BY ORDER, X_FIELD

I got a diferent result from the one I am expecting (above).

Thanks for your help,

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Try this:

Inner Join (TABLE1)

LOAD ORDER, MAX(DATE) AS DATE

RESIDENT TABLE1

GROUP BY ORDER

;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

View solution in original post

3 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Try this:

Inner Join (TABLE1)

LOAD ORDER, MAX(DATE) AS DATE

RESIDENT TABLE1

GROUP BY ORDER

;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

maxgro
MVP
MVP

TABLE2:

load

  ORDER,

  FirstSortedValue(X_FIELD, -DATE),

  FirstSortedValue(DATE, -DATE)

Resident TABLE1

group by ORDER;

Not applicable
Author

Beautiful. Thanks Rob!