Once a query has been made, you can make a few refinements to the data before it is saved. The last function at the bottom is a critical step: specifying the file to be saved.
Write to Text File
You specify the file name, which is a combination of the folder, a base file name, plus an optional date/timestamp, and a file type (comma-separated/CSV or tab-separated/TSV). An example of the file name will appear below when you click out of the boxes.
You can control the format for any date columns in your data — click See Codes for a help document about code options available.
Note that if you do not want to use the default ‘.csv’ or .tsv’ file extensions, you can enter the extension you want in the base file name e.g. ‘by country.txt’. The File Type selection will still control whether the data is comma-separated or tab-separated, but the file will be saved with the extension you entered.
Quick Functions
When the quick functions wizard pops open, a temporary (green) sheet will appear behind the wizard showing what the data will look like with the transformations you have selected.
Pivot Function
The pivot function lets you pivot one column to the columns, aggregating a single metric to the cells.
Pivot to Columns – select a column (optionally By Name) to pivot or choose No Pivot.
Columns to Keep – keep only the columns you want.
Cell Values – select a column to be used for the values in the cells of the pivoted table.
Aggregation – select the aggregation to use when there are duplicate values for a specific cell position.
Arrange Function
The arrange function lets you reorder the columns, either by name or by position.
No Change – do not change the order of the columns.
By Name/Position – Use this option to rearrange the columns based on the positions (column letters), or By Name.
Sort By Name – Use this option to arrange the columns by the names of the columns themselves,. Select the columns you want sorted, then the sorting options.
Filter Function
Create one or more filter rules which will be applied in sequence. Note that the data in the temporary (green) sheet in the background changes as you add filters.
Add Filter
Column – you can select columns By Name or by position (column letter).
Criteria – pick the filter criteria.
Value – enter the value for the filter.
Add – add a new filter rule with the column, criteria and value.
Filter Rules – you can change the order of the filter rules, or remove any of the rules.
Sort Function
Create one or more sorting rules which will be applied in sequence. If two rows are equal in value for the first rule, the next rule is used to determine the order.
Add Sort
Column – you can select columns By Name or by position (column letter).
Ascending/Descending – adds the selected column to the Sorting Rules.
Sorting Rules – you can change the order of the sorting rules, or remove any of the rules. Sorting uses the rules from the top down; the second rule is used only after the first rule is run.
Count – after the sorting rules are applied, you can keep the top 10 or whatever number of rows you want.