topic Re: Group By one column only - max date in QlikView

Group By one column only - max date

Wed, 26 Aug 2015 19:37:57 GMT

Hi Qlik Communty:

So, I have this Table1:

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

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 — Wed, 26 Aug 2015 19:50:58 GMT

Try this:

Inner Join (TABLE1)

LOAD ORDER, MAX(DATE) AS DATE

RESIDENT TABLE1

GROUP BY ORDER

;

-Rob

maxgro — Wed, 26 Aug 2015 19:51:29 GMT

TABLE2:

load

ORDER,

FirstSortedValue(X_FIELD, -DATE),

FirstSortedValue(DATE, -DATE)

Resident TABLE1

group by ORDER;

Wed, 26 Aug 2015 20:04:34 GMT

Beautiful. Thanks Rob!