#71 — Take Values of Adjacent Rows in The Same Group (Search & Filter within Adjacent Intervals)

#productivity #beginners #programming #tutorial

Problem description & analysis:

There is an Excel table:

Task: Now we want to add two columns, PreviousDailySales and NextDailySales, to fill in the sales of current product on the previous selling day and the next selling day, respectively.

Analysis: The data is sorted by date first, and then by product. If the rows with same product are regarded as a group, the problem will change to taking the values of the previous row and the next row in the same group. The difficulty of the problem is how to find the previous and next rows in the same group without changing the order of rows.

Solution:

There are two ideas to solve this problem (fill in the code in cell D1):
1. Search and filter in adjacent intervals: directly search forward and backward without changing the order of data. Once the row of the first product with the same name is found, it is the sales of the previous selling day/next selling day.

     A
 1  =E(‘A1:C2401’)
 2  =A1.derive(~[:-1].select@1z(ProductName==A1.ProductName).Sales:PreviousDailySales, ~[1:].select@1(ProductName==A1.ProductName).Sales:NextDailySales)
 3  return A2.new(PreviousDailySales,NextDailySales)

A2: ~[:-1] represents the set of all rows from the beginning to the previous row, and ~[1:] represents the set of all rows from the next row to the end.

2. Take the value of adjacent rows within the same group: group the data by product, and take the value of the previous row/the next row within the group directly, which is the sales of the previous selling day/next selling day.

     A
 1  =E(‘A1:C2401’).derive(:PreviousDailySales,:NextDailySales)
 2  =A1.group(ProductName).run(~.run(PreviousDailySales=Sales[-1], NextDailySales=Sales[1]))
 3  return A1.new(PreviousDailySales,NextDailySales)

A2: Sales[-1] represents the value of column Sales of the previous row, Sales[1] represents the value of column Sales of the next row.

The result is shown in the picture below: