#71 — Take values ​​from adjacent rows in the same group (search and filter within adjacent intervals)

Problem description & analysis:

There is an Excel table:

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

Analysis: The data is sorted first by date and then by product. If the rows with the same product are considered as a group, the problem changes 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 the rows.

Solution:

There are two ideas to solve this problem (enter the code in cell D1):
1. Search and filter in adjacent intervals: Instantly search forward and backward without changing the order of the data. Once the row of the first product with the same name is found, this will be the turnover of the previous sales day/next sales 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 immediately take the value of the previous row/the next row within the group, which is the turnover of the previous sales day/next sales 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 Sales column of the previous row, Sales(1) represents the value of Sales column of the next row.

The result can be seen in the image below:

Feel free to download esProc Desktop for FREE and master advanced filtering techniques in no time!! 🚀🔥⬇️

✨SPL Download Address: esProc Desktop FREE Download

✨Plugin installation method: SPL XLL installation and configuration

✨References to other rich Excel editing cases: Desktop and Excel data processing cases

✨FREE YouTube Courses: SPL Programming