CODEWITHSUNDEEP
palantir-foundryfoundry-contour
Adil B
Adil B

Reputation: 16778

In Contour, how do I filter down to the top N by values in a group?

In an analysis where each row contains e.g. a flight number, I'd like to filter down my active dataset to keep only the three most-delayed flight numbers for each carrier.

How do I accomplish this in Contour?

Upvotes: 1

Views: 1517

Answers (1)

Andrew St P
Andrew St P

Reputation: 584

This is a two-step process: (1) Group and order, and (2) filter.

Group and Order: Add an Expression board, and make a new column by assigning a row number for each record. Reset the row number within each carrier group, and ensure that row numbers are assigned in order of flight delay duration.

row_number() OVER ( PARTITION BY "carrier" ORDER BY "delay_duration" DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )

Filter: Add a Filter board and select those row numbers created above that are less than or equal to 3 to keep only the three most delayed flights for each carrier.

Upvotes: 2

Related Questions