"Task description Sales table sales.xlsx contains data as follows: [pic] The task is to .."

blackduckie RaqForum 28 No.
1 Reply • 314 View • 3 Years ago

How to Select Records by Specified Percentage of a Specific Field

Task description

Sales table sales.xlsx contains data as follows:

The task is to find the product whose sales Revenue occupies over 70% of the total Revenue in the current month based on the records of each SalesName.

Directions:

Write script in esProc:

	A
1	=clipboard().import@t().derive(Percentage)
2	=A1.group(SalesName,Month)
3	=A2.run(a=~.sum(Revenue),~.run(Percentage=Revenue/a*100))
4	=A1.select(Percentage>70).new(SalesName,Product,Month,Revenue)
5	=clipboard(A4.export@t())

A1 Import data from clipboard and add a new column Percentage; @t option enables reading the first row as column headers.

A2 Group rows by SalesName and Month.

A3 Loop through each group to sum Revenues and assign the result to variable a, and then loop over each record in each group to calculate the Percentage.

A4 Get records where Percentage is greater than 70, and select SalesName, Product, Month, and Revenue columns to create a new data set.

A5 Convert A4’s result set to strings and export them to clipboard. @t option enables reading the first row as column headers.

Switch back to Excel, select data area A1:D13, and press Ctrl+C to copy it to clipboard.

In esProc, press F9 to execute the program, and switch back to Excel after the execution is finished to click F1, and press Ctrl+V to paste the result in.