How to get latest one row for every student in Straight Table?

[Anonymous]

Hi,

I am not sure my problem is difficult or I am not looking at it right. Please help me.

In my QlikView script, have a SQL query to get top 3 rows from database. It gives TMK, StudentID, StudentName, StudentMarks as dimensions. Now, I have to take the top row for each student from all months, and display all other details in a straight table.

TMK          ID     Name     Marks

201701     1          A          20

201702     1          A          30

201703     1          A          10

201701     2          B          40

201702     2          B          50

201703     2          B          10

In this, I need 201703 records(A-201703     1          A          10, B- 201703     2          B          10)for both the students. Can we do this directly in the chart rather than using QV script? I could only find Max(TMK) using calculated dimension/expression. But I don't know how to display an entire record as they are loaded as dimensions. Thanks in advance.

[boorgura]

Use ID and Name as dimension.

And, the following expressions:

TMK: max(TMK)

Marks: only({<TMK = {"$(=max(TMK))"}>} Marks)

[sunny_talwar]

Are you using Straight table to display this? If you are you can do this

Dimension

TMK

ID

Name

Expression

Max(TMK)

Sum({<TMK = {"$(=Max(TMK))"}>}Marks)

The above expression is assuming that Max(TMK) will be same across all the ID and Name and if for any reason a ID has a max TMK of 201702... it will not show up on this chart because this chart is showing TMKs for 201703 (without selection)

[Anonymous]

Thank you Sunny, for pointing this out, which is exactly my requirement. Sorry, my example data was not demonstrating this. So, if there is a possibility of different Max(TMK) for each student, then how do we do that? Thank you again!

[Anonymous]

Thank you Rakesh! As I was telling, there is a possibility that each student might have different Max(TMK), then how do we solve this? Any help is appreciated. Thanks again.

[sunny_talwar · Accepted solution]

Try this

FirstSortedValue(Aggr(Sum(Marks), TMK), -TMK)

Or this

Sum(Aggr(If(TMK = Max(TOTAL <ID> TMK), Sum(Marks)), ID, TMK))

[Anonymous]

Thank you, Sunny.

Second expression worked as required. I understood half of it (could say only Aggr part). Could you please explain the logic so I will be able to understand it clearly. Thank you once again.

[sunny_talwar]

It is just saying that Sum(Marks) where TMK = Max TMK for each ID