Group By one column only - max date

[#1]

Hi Qlik Communty:

So, I have this Table1:

TABLE1:
ORDER	X_FIELD	DATE
10001	2	20/08/2015
10001	4	15/08/2015
10002	4	19/08/2015
10002	4	20/08/2015
10002	2	21/08/2015
10003	4	18/08/2015
10003	2	20/08/2015

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

ORDER	X_FIELD	DATE
10001	2	20/08/2015
10002	2	21/08/2015
10003	2	20/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,

[rwunderlich · Accepted solution]

Try this:

Inner Join (TABLE1)

LOAD ORDER, MAX(DATE) AS DATE

RESIDENT TABLE1

GROUP BY ORDER

;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[maxgro]

TABLE2:

load

  ORDER,

  FirstSortedValue(X_FIELD, -DATE),

  FirstSortedValue(DATE, -DATE)

Resident TABLE1

group by ORDER;

[#4]

Beautiful. Thanks Rob!