Edit result

Read(353) Label: edit result,

After a result set is renamed, we can perform operations like add, delete, modify, and export on it. For example, we click to rename the above result set Emp:

  Click “OK” to get the following interface displaying the result set:

Edit data

This section explains common operations on a result set, such as add, delete, and modify.

Append row

Click  to add a row after the last row of the result set.

Append column

Click  to add a column after the last column of the result set.

Insert row

Click  to insert a row before a selected row or cell.

Insert column

Click  to insert a column before a selected column or cell.

Delete row

Click  to delete one or more selected rows.

Delete column

Click  to delete one or more selected columns.

Shift row up

Click  to move up one or more selected rows.

Shift row down

Click  to move down one or more selected rows.

Shift column left

Click  to move the current column left.

Shift column right

Click  to move the current column right.

Modify

Select a cell and double click it to modify data in the cell.

Copy

Select one or more rows, right click them, and select copy option to copy the selected row(s) to clipboard.

Cut

Select one or more rows, right click them, and select cut option to cut the selected row(s).

Paste

Select one or more rows, right click to select paste option to paste data on the clipboard to the current position. Note that the action requires that data structure on the clipboard be the same as that of the current table.

Complex computation

This section explains how to perform complex computations on data, such as filtering, sorting and grouping.

Filter data

To perform filtering on one or more fields, right click a result set to choose “Filter data”, and select records according to the specified filter expression. To get records employees in sales department whose salary are above 5,000 from result set Emp, the filter expression is DEPT == "Sales" && SALARY > 5000, as shown below:

Click “OK” and we get the following result:

In a filter expression, we can also use an esProc function besides operators. To further filter the above result set to get records of employees whose names start with letter J, the filter expression is like(NAME,"J*"), as shown below:

Click “OK” and we get the following result:

Sort data

Double click a field on the table header to sort the current column. To sort result set Emp by SALARY, double click SALARY field to sort values in ascending order:

Double click SALARY field again to sort values in descending order. We can also drag field names to change order of fields.

Group data

In “Edit command” zone, we can group a result set through executing a SPL command. For example, to group result set Emp by DEPT and calculate average salary in each group, we enter the following SPL command:

SPL: Emp.groups(DEPT;avg(SALARY):AVG_Salary). Click “Execute” and get the following result:

Learn more about SPL grouping operations HERE.

Set operations

We can perform a set operation on multiple result sets through executing a SPL command. For example, we have result sets StockRecord2013 and StockRecord2014, and trying to union them. Below are contents of the two result sets:

Execute command [StockRecord2013,StockRecord2014].union()

And get the following result:

Besides using union function to perform a union operation, we can use the union operator & to do it. So, the above command can be written as StockRecord2013&StockRecord2014, which gets a same result.

Similarly, in SPL we perform an intersection operation through [A,B].isect() or A^B, a difference operation through [A,B].diff() or A\B, and a concatenation operation using [A,B].conj() or A|B.

Data association

We can associate multiple result sets through a SPL command. Now we try to associate the two tables StockRecord2013 and StockRecord2014 according to data positions.

Execute the following command:

join@p(StockRecord2013;StockRecord2014).new(~.#1.SID,~.#1.Date,~.#1.Closing,~.#2.Date,~.#2.Closing)

And get the result:

Save data

This section explains how to save an edited result to a file.

Save as text

Click  and we can save the current result set as a text file of txt fomart.

Export to file

Click  and we can export the current result set as file of a certain format. Supported file formats include ctx, btx, txt, csv, and xlsx. Below is the export interface:

Set name of the file to be exported, select export type and options, check fields to be exported and the primary key, and click “OK” to export data in a result set to a file.

Export column headers: Write field names (titles) to the first row of the file.

Append: Append-write; the appended contents need to have same structure as the target file, otherwise error will be reported.

Write to binary file by segment: Write data as a faster binary file.

Use Windows-style line break: Use Windows-style line break, which is \r\n; otherwise use system default.

Enclose field values and headers in quotes: Enclose exported text field values and names with quotes.

Use double quotation marks as escape character: Convert two double quotation marks into one and do not escape other characters.