Select field based on maxdate

[Anonymous]

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

[martin59]

Hi,

You have to do something like that :

=firstSortedValue(Color,-Date)

Hope that helps you

[fernandotoledo]

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
;

[Anonymous]

Worked perfectly - thanks

[Anonymous]

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).