Get most-recent value of one field based on date in another field?

[#1]

I am trying to get the most-recent value of field1 based on the value of another field (field2), which is a date.  For example, imagine a Score table that captures student's scores with three fields:  Student, Date, Score:

Student	Date	Score
Chris	7/14/15	89
Mark	7/14/15	92
John	7/14/15	93
Mark	7/13/15	87
Chris	7/13/15	88
John	7/12/15	90

I'm trying to plot the student's name and last score in a table:

Student	Last Score
Chris	89
Mark	92
John	93

Any idea what that expression would look like?

Thanks!

[swuehl · Accepted solution]

Try this in a chart with dimension Student

=FirstSortedValue(Score, -Date)

assuming Date is a dual value with a numeric representation.

[sunny_talwar]

Sample qvw showing swuehl‌'s recommendation

and an alternative where you can create a flag in the script:

Table:

LOAD Student,

    Date,

    Score

FROM

[https://community.qlik.com/thread/172583]

(html, codepage is 1252, embedded labels, table is @1);

Join(Table)

LOAD Max(Date) as Date,

  Student,

  1 as Flag

Resident Table

Group By Student;

Output in a table box showing the flag and straight table showing the result you are looking for:

HTH

Best,

Sunny

[jagan]

Hi,

In front end try like this

Dimension: Student

Expression: Sum({<Date={'$(=Max(Date))'}>}Score)

If you want this in backend script then use below script

Table:

LOAD Student,

    Date,

    Score

FROM

[https://community.qlik.com/thread/172583]

(html, codepage is 1252, embedded labels, table is @1);

Left Join(Table)

LOAD Max(Date) as Date,

  Student,

  1 AS MaxDateFlag

Resident Table

Group By Student;

Now in front end

Dimension: Student

Expression: Sum({<MaxDateFlag={1}>}Score)

Hope this helps you.

Regards,

Jagan.

[#5]

Thanks for assisting  swuehl‌.

Date for me is a Date value created via the Date function in the load script:  Date("input field")

Can you explain this statement?

     assuming Date is a dual value with a numeric representation.

Does that mean I should use Dual("input field") in the load script?

I found this article about a Dual() function:  Dual &amp; Exists – Useful Functions‌. Is that what you mean by dual value?

Thanks again

[sunny_talwar]

Date is a dual function and you don't have to reuse another Dual() on top of it. My guess is that the reason Stefan asked you if Date is dual is not, is because there are times when Date is not recognized as date or when you create date this way Month(Date) & '|' & Year(Date). The above two may look like dates, but they are actually text which cannot be sorted by FirstSortedValue() function.

Please free to correct me if I am wrong Stefan.

HTH

Best,

Sunny

[swuehl]

Please have a look at

Get the Dates Right

Data Types in QlikView

Why don’t my dates work?

These blog posts should answer all your questions.