Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
Try this:
Inner Join (TABLE1)
LOAD ORDER, MAX(DATE) AS DATE
RESIDENT TABLE1
GROUP BY ORDER
;
-Rob
Try this:
Inner Join (TABLE1)
LOAD ORDER, MAX(DATE) AS DATE
RESIDENT TABLE1
GROUP BY ORDER
;
-Rob
TABLE2:
load
ORDER,
FirstSortedValue(X_FIELD, -DATE),
FirstSortedValue(DATE, -DATE)
Resident TABLE1
group by ORDER;
Beautiful. Thanks Rob!