topic Re: How to get latest one row for every student in Straight Table? in QlikView

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

boorgura — Tue, 10 Apr 2018 15:11:58 GMT

Use ID and Name as dimension.

And, the following expressions:

TMK: max(TMK)

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

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

sunny_talwar — Tue, 10 Apr 2018 15:12:29 GMT

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

Dimension

TMK

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)

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

Anonymous — Tue, 10 Apr 2018 19:04:11 GMT

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!

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

Anonymous — Tue, 10 Apr 2018 19:06:24 GMT

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.

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

sunny_talwar — Tue, 10 Apr 2018 19:40:52 GMT

Try this

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

Or this

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

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

Anonymous — Fri, 13 Apr 2018 16:28:18 GMT

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.

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

sunny_talwar — Fri, 13 Apr 2018 16:29:47 GMT

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