Allows the user to determine a value from a preceding or following row at a given offset within a group (or partition) of values.
|Name||Text||The descriptive name for the component.|
|Include Input Columns||Yes/No||Defines whether the component passes all input columns into the output.|
|Partition Data||List of Columns||Defines how the input data is partitioned in order to perform the rank calculation. The calculation is then performed on each partition.|
|Ordering within partitions||Input Column||The input column name for sorting within the partitioned data. Note: you can drag to reorder|
|Ordering||The order of the sorting: Ascending (Asc) or Descending (Desc)|
Returns the column from <offset> rows earlier in the partition
|Input Column||The name of the output column that the lead/lag function will return.|
|Offset||The number of rows to go forward (lead) or backwards (lag) in the partition.|
|Output Column||The name of the output column that the window function will create.|
|Ignore Nulls||Yes/No||Disregard Null values when determining which row to use. Null values do not count toward reaching the offset.|
This example uses the flight data. It uses an aggregation to calculate the total flight time per day and then uses the lead lag to add the flight time from the prior day and the prior prior day for comparison.
Note: The aggregation component groups the data by Year, Month and DayOfWeek and totals the airtime per day (see Aggregate Component for more detail)
The component properties are set up as below. The data is partitioned down to individual months and ordered by day.
The Functions are set up to use the Lag Window Function. Two new Lag columns are created from sum_airtime called Airtime Prior Day and Airtime Prior Prior Day. The former has an offset of one hence the day before the current row, and the latter an offset of 2, two days before the current row.
The Sample data shows the new columns added to the result.