"There is an Excel table: [pic] Now we want to add two columns, PreviousDailySales and NextDailyS .."

mars RaqForum 25 No.
216 View • 1 Years ago

6.9 Take values of adjacent rows in same group (search & filter within adjacent intervals)

desktop and excel data processing cases(180)

There is an Excel table:

imagepng

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 of the problem: 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.
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)