Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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!

1 Solution

Accepted Solutions
MVP
MVP

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.

6 Replies
MVP
MVP

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.

MVP
MVP

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

Sample qvw showing swuehl‌'s recommendation

Capture.PNG

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:


Capture.PNG

HTH

Best,

Sunny

MVP
MVP

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:

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.

Not applicable

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

Thanks again

MVP
MVP

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

MVP
MVP

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

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.

Community Browser