First/Last Component

First/Last Component

Allows the user to create groups of data and then return only the first or last rows from the group.

Properties

Property Setting Description
Name Text The descriptive name for the component.
Grouping Columns List of Columns Defines how the input data is grouped, this works like an SQL group by. The first or last element of each group will be selected.
Ordering within partitions Input Column The input column name for sorting within the grouped data. Note: you can drag to reorder
Ordering The order of the sorting: Ascending (Asc) or Descending (Desc)
First/Last Columns Column The name of the input column to be passed to the ouptut
First/Last Return the First or Last Element of the chosen column in the grouped data.

Strategy

Generates a select statement with a window function in line using the OVER keyword.

Example

This example returns the first flight for a given plane in a given year.

Note: The filter removes some null data rows.

The component properties are set up as below.

Note: we group by Plane (tailnum) and then year to get the correct aggregation and then sort by the date for each plane (tailnum, year,month,dayofmonth)

For each column not involved in the grouping we must specify First or Last if we want to see that column in the output.

The output data shows the date of the first flight for each plane.