topic Select field based on maxdate in QlikView

Select field based on maxdate

Anonymous — Mon, 04 Apr 2011 15:14:37 GMT

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

Re. :Select field based on maxdate

martin59 — Mon, 04 Apr 2011 15:19:35 GMT

Hi,

You have to do something like that :

=firstSortedValue(Color,-Date)

Hope that helps you

Select field based on maxdate

fernandotoledo — Mon, 04 Apr 2011 15:29:35 GMT

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
;

Re. :Select field based on maxdate

Anonymous — Mon, 04 Apr 2011 15:46:21 GMT

Worked perfectly - thanks

Re. :Select field based on maxdate

Anonymous — Mon, 04 Apr 2011 15:52:29 GMT

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