Filtering ORDERBY output to unique rows

[Stelio_Pappas]

Hi all

First, I just want to say a quick thank you to everyone who helps out here.

Second, sorry for the long post.

I’m building an event management system using data that is imported from a third party member management system. Each week I import a list of members from a CSV file as more students sign up to a sporting competition we are running.

BACKGROUND

My Columns (inherited from a CSV file)
The Members table includes the following columns; FirstName, LastName, DateOfBirth, SchoolName, and SchoolLevel.

My Virtual Columns
Members[ComputedMemberID]
This is the athlete’s name and date of birth concatenated to form the key of the Members table.

Members[SeriesPoints]
A SUM of points that athletes have earned over a series of events in another table.

Members[SchoolPoints]
A SUM of all the points from athletes who attend the same school.

I’ve figured out how to output a rank of students to a text file (using bots), however ranking schools with the absence of a Schools table is proving to be difficult.

Secondary Schools<<Start:
                    ORDERBY(
                        SELECT(
                            Members[ComputedMemberID],
                            AND(
                                [SchoolLevel] = "Secondary",
                            )
                        ),
                        [SchoolPoints],
                        TRUE
                    )
                 >>
<<SchoolPoints>> <<SchoolName>><<End>>

The problem is the output produced includes multiple lines as the ORDERBY encounters each student at the school. The output should be a list of schools ranked by pointscore, unfortunately I have duplicates. -My output looks something like this:

Secondary Schools
538 School A
538 School A
538 School A
441 School B
441 School B
287 School C
270 School D
196 School E

From what I can tell, the ORDERBY function only seems to work on the ComputedMemberID (key) and not other columns. When I try to combine it with the UNIQUE function it only seems to work against the key, rather than the [SchoolName]. This makes sense, but doesn’t help me.

I think this would be easy if I had a Schools table, but I’m trying to avoid importing a second CSV file for unique Schools if possible.

My Question
Is there a way to limit the output to not include duplicate rows?

[Suvrutt_Gurjar · Accepted solution]

Please try below

Please create a slice called say “SchoolsByPoints” on the Members table with an expression something like

[ComputedMemberID] =MINROW(“Members”, “SchoolPoints”, AND([School Name]=[_THISROW].[School Name] , [SchoolLevel]=“Secondary”))

Please base your START expression based on this slice.

Secondary Schools << Start: SchoolsByPoints[ComputedMemberID] >>
<< SchoolPoints >> << SchoolName >> << End >>

[Stelio_Pappas]

You’re a genius @Suvrutt_Gurjar. Thank you!

[Suvrutt_Gurjar]

Thank you for the update @Stelio_Pappas . Good to know it works.

Just to update we used MINROW() expression to get only one record for every school at the “Secondary” school level.

Like ORDERBY(), the expressions MINROW(), MAXROW() also work with key column references. However, they return either minimum or maximum row reference for a given condition as against a list by ORDERBY()

In this particular case, we could have used MAXROW() as well, since the main intention was to get one row per school.

help.appsheet.com

MINROW()

Row with lowest value in column

[Stelio_Pappas]

Yes. -Thank you. Once I had the unique table from the MINROW, I was able to wrap it with an ORDERBY.

Again, thank you very much for your help.

Stel