topic Lookup the most recent value in QlikView

Lookup the most recent value

Wed, 08 Aug 2012 08:44:13 GMT

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.

Re: Lookup the most recent value

Miguel_Angel_Baeyens — Wed, 08 Aug 2012 08:46:13 GMT

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

Re: Lookup the most recent value

Fri, 24 May 2013 19:26:41 GMT

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

Re: Lookup the most recent value

Miguel_Angel_Baeyens — Sat, 25 May 2013 18:54:37 GMT

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