Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

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

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.

1 Solution

Accepted Solutions
MVP

Try this

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

Or this

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

7 Replies
Specialist

Use ID and Name as dimension.

And, the following expressions:

TMK: max(TMK)

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

MVP

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
Not applicable
Author

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
Not applicable
Author

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.

MVP

Try this

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

Or this

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

Anonymous
Not applicable
Author

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.

MVP

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

Community Browser