"Below is the daily product sales amount table: OrderDate ProductName Sales 1996-07-04 Aniseed Sy .."

mars RaqForum 25 No.
264 View • 2 Years ago

1.20 Order-based computation: filter in neighboring interval

Below is the daily product sales amount table:

OrderDate	ProductName	Sales
1996-07-04	Aniseed Syrup	51919.0000
1996-07-04	Chai	21169.0000
1996-07-04	Chang	23154.0000
1996-07-04	Chef Anton’s Cajun Seasoning	20307.0000
1996-07-04	Chef Anton’s Gumbo Mix	97636.0000
…	…	…

The task is to add two columns, PreviousDailySales and NextDailySales that will contain sales amounts of the current product in the previous sales date and in the next sales date respectively.

Task analysis: The original data is ordered by date and then by product. If we treat rows of same product as one group, the task is transformed to getting specified values of the previous row and the next row in same group. Yet the difficulty is how to locate the previous row and the next row in one group while maintaining the order of records.

Solutions:

1.Search and filter in neighboring interval: Based on the original order of the data, perform the search forward or backward to get the first record of same product, which contains the sales amount of the previous/next sales date.

	A
1	=T(“ProductDailySales.xls”)
2	=A1.derive(~[:-1].select@1z(ProductName==A1.ProductName).Sales:PreviousDailySales, ~[1:].select@1(ProductName==A1.ProductName).Sales:NextDailySales)

A2 ~[:-1] represents a set covering all records from the beginning to the previous one; ~[1:] represents a set including all records from the next one to the end.

2.Get value from the neighboring row within one category: Group the original data by product and get value of the specified field in the previous or next row, which is the sales amount of the previous/next sales date.

	A
1	=T(“ProductDailySales.xls”).derive(:PreviousDailySales,:NextDailySales)
2	=A1.group(ProductName).run(~.run(PreviousDailySales=Sales[-1], NextDailySales=Sales[1]))
3	return A1