|1010data for Excel users | Calculations ||
Filtering and sorting the data in a table based on specified criteria can be useful for both completing aggregations and viewing the data.
You would like to filter your table to only include data with specific qualities. Additionally, you would like to then sort the remaining data by a chosen column. You know how to do this in Excel by using the filter and sort tools, and you would like to produce the same results with 1010data.
Within the Editing portion of Excel's toolbar, there is a Sort & Filter section. Upon highlighting the spreadsheet in its entirety and selecting filter, you then have the option to filter by each column. For this analogue, you want to show data containing department 13 and groups 501 and 196. Therefore, by using the drop-down on the Department and Group columns, you use these selections as the filters. Then to sort, you chose the desired column and select either Sort A to Z or Sort Z to A. This analogue uses the former. The final spreadsheet is shown below.
Although Excel offers an easy solution to this problem, 1010data offers an equally simple solution. Additionally, 1010data can process more data at a faster speed, making it ideal for larger data sets.
1010data's GUI offers a similar feel to the format of Excel. Within the Select Rows dialog, you are able to filter the table based on multiple criteria. For this analogue, you want to select data that contains department 13 and group 501 or 196. The image below shows these options selected from the drop-down menus and entered in the appropriate text fields. Upon clicking Select, the filters will be applied to the table.
As can be seen in the next image, each column in your table contains sort icons. These allow you to sort the table based on the selected column. In this analogue, the table is sorted by item description from A to Z, therefore the Sort Ascending icon is selected.
The final table with all filters and sorting applied can be seen below.
<base table="pub.doc.retail.altseg.products"/> <sel value="(dept=13)&(group=196 501)"/> <sort col="description" dir="up"/>
By entering this macro code into the Edit Actions dialog, you can achieve the same results as the 1010data GUI solution. The <sel> operation allows you to filter the data, and multiple criteria can be combined by using & or |. Then, the <sort> operation sorts the specified column by the supplied direction, up or down.