6 Replies Latest reply: Jul 15, 2015 12:32 PM by Stefan Wühl

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

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:

StudentDateScore
Chris7/14/1589
Mark7/14/1592
John7/14/1593
Mark7/13/1587
Chris7/13/1588
John7/12/1590

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

StudentLast Score
Chris89
Mark92
John93

Any idea what that expression would look like?

Thanks!

• ###### Re: Get most-recent value of one field based on date in another field?

Try this in a chart with dimension Student

=FirstSortedValue(Score, -Date)

assuming Date is a dual value with a numeric representation.

• ###### Re: Get most-recent value of one field based on date in another field?

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 & Exists – Useful Functions . Is that what you mean by dual value?

Thanks again

• ###### Re: Get most-recent value of one field based on date in another field?

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

• ###### Re: Get most-recent value of one field based on date in another field?

Get the Dates Right

Data Types in QlikView

Why don’t my dates work?

• ###### Re: Get most-recent value of one field based on date in another field?

Sample qvw showing swuehl's recommendation

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

Table:

Date,

Score

FROM

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

Join(Table)

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

• ###### Re: Get most-recent value of one field based on date in another field?

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:

Date,

Score

FROM

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

Left Join(Table)

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.