I’d like to show a few usages of the Look ahead/behind step.
The step makes previous and upcoming rows available so you can compute fields for the current row that depend on other rows surrounding it.
Using Previous / Next rows
In this use case we o process the following data:
We have invoices, line item numbers, and associated prices. The requirement is to add a column for a running sum per invoice, and an additional row for each invoice that holds the total.
In effect, we want to arrive at this:
A possible solution is to use the Look ahead/behind step to mark the first and last row of an invoice.
Comparingthe current invoice_id with the previous and next row allows us to set the markers like so.
Subsequent steps can use the first marker to reset the running sum, and the last marker to insert a row containing the total.
See running-sum-and-total.dfl (26.9 KB) for a complete example.
A sliding window of surrounding rows
In this use case, we have a sequence of measurements. For each measurement, we want to calculate its average based on current, two previous, and two subsequent measurements.
Starting with column m, we want to establish its surroundings and take the average like so:
The look ahead/behind step gives us a list of previous and next rows to work with. We can extract the measurements from the surrounding rows and calculate our average.
See surroundings.dfl (11.6 KB) for a complete example.