Lookup the most recent value

[#1]

New to qlikview.

Imagine a listing of a baseball players statistics by year.  This includes information like the Team that he played for that year, and his stats.  My table includes only one record per player per year.

I want to be able to report only the most current team that player plays for.  Right now, my expression is =Team , this works when i have the year as a selection in a list box, but I want always the current team to shown, regardless of the list box selection.

[Miguel_Angel_Baeyens]

Hi,

Use the function FirstSortedValue() in either the script or any object. Assuming you have a Date field, in a straight table chart with Player as dimension, then the expression should be

FirstSortedValue(Team, -Date)

Hope that helps.

Miguel

[#3]

Miguel,

I am trying to use FirstSortedValue in my script, and it bombs every time. Here's what I have:

Load

FirstSortedValue(EVT_NOTES,EVTD_Date,1) As EVNote1,
*;


SQL
SELECT
      [EVT_Type]
      ,[EVT_Reference_ID]
      , '{' + UPPER([EVT_Reference_ID]) + '}' AS [AUC_UID]
      ,[EVTD_Date]
      ,[EVT_USER]
      ,[EVT_NOTES]
      ,[EVT_ID]
      ,  CAST(Year(EVTD_Date) AS CHAR(4))+'-'+RIGHT('0' + RTRIM(Cast(MONTH(EVTD_Date) AS CHAR(2))),2) AS YearMonth

  FROM Webadmin.dbo.Eventlog

  WHERE [EVT_Type] = 'audit';

If I comment out the LOAD statement, everything works fine. If I leave the LOAD statement in, I get an OLEDB read failure. Why is this happening?

Thanks,

Kevin

[Miguel_Angel_Baeyens]

Hi Kevin,

Is the EVTD_Date field a numeric value? Note that FirstSortedValue() is an aggregation function (will need a GROUP BY) and the second parameter needs to be numeric.

Hope that helps.

Miguel