Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Thanks in advance for any replies!
Using =max(Date), we get the latest date from a table we have joined to
That works great
However, we also need to display in the next field, the associated value for that record, based on =max(Date)
Example data:
Main Table - Ref 123456
Linked table:
Ref 123456 - 21/03/2011 - red
Ref 123456 - 25/03/2011 - blue
Ref 123456 - 29/03/2011 - green (THEREFORE =max(Date))
In the above example we would like to pull 29/03/2011 for =max(Date), and 'green' based on =max(Date) of 29/03/2011
Hi,
You have to do something like that :
=firstSortedValue(Color,-Date)
Hope that helps you
You may treat this in the script, like
load
DATE,
if(DATE=DATE_MAX,'green','blue') AS COLOR,
A,
B,
C
;
load
DATE,
max(DATE) as DATE_MAX,
A,
B,
C
FROM TABLE1
GROUP BY A,B,C
;
Worked perfectly - thanks
Firstsortorder returns null, if his max(Date) is on more than one record, right?
I'm not sure I read your data model correct, but they are in same table right? How about using FieldValue and FieldIndex to pull it out: FieldValue('Colour',FieldIndex('Date',max(Date))). Maybe with some sorting during load, if more than one record can hold the max(Date).