Quick Queries in the Analytics Edge Core Add-in allow you to make several transformations to your data before you write it into your worksheet. These “Quick Functions” are available only to Premium license users.
Once your query has been created, a Functions Wizard pops up offering you the ability to Pivot, Arrange, Sort or Filter your data as well as options for when you Write To Worksheet.
When editing a query, any tabs that have non-default settings will be *highlighted* with asterisks so you know they contain special selections. If you change the query itself, some of these selections may need to be updated (such as selected columns or filter values).
Pivot Function
Columns – select and rearrange the columns you want to keep in the resulting table. You can identify columns by position (column letter) or By Name.
Pivot to Columns – select a column to pivot, so a new column will be created for each unique value in the selected column. Select ‘– No Pivot‘ to leave the data as-is.
Cell Values – select a column to be used for the values in the cells in the pivoted table.
Aggregation – select the aggregation to use when there are duplicate values for a specific cell position.
Arrange Function
No Change – do not change the order of the columns.
Arrange By Name/Position – Use this option to rearrange the columns based on the positions (column letters) of the columns, or By Name. This is useful when you get imported data that may have additional or out-of-order columns.
Sort By Name – Use this option to arrange the columns by the names of the columns themselves, in a sorted order. Select the columns you want sorted, then the sorting options. Note: the columns are identified by the column letters, not the actual names themselves, and it allows the names to change over time.
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. You can specify as many rules as required.
Column – you can select columns By Name or by position (column letter).
Add – select a column and sort order (Ascending/Descending), then click Add to add it to the Sorting Rules.
Sorting Rules – you can change the order of the sorting rules, or remove any of the rules using the arrows and X buttons. Sorting uses the rules from the top down; the second rule is used only after the first rule is run.
Filter Function
Column – you can select columns By Name or by position (column letter).
Add – select a column and filter criteria (comparison and value), then click Add to add it to the Filter Rules. Filters will be combined using AND logic (rows must pass all filters to ‘match’), and you can choose to Keep or Remove the matching rows. While typically used to remove unwanted data from a report, it can also be used to selectively keep rows of interest.
Filter Rules – you can change the order of the filter rules, or remove any of the rules using the arrows and X buttons.
Write to Worksheet Function
Write to Worksheet – replace the previous query results.
Clear data and formatting – clears the existing data and formatting from the worksheet. The existing data may contain more rows and columns than the new data, so the add-in clears the block of contiguous data it finds at the target cell.
Do not clear cell formatting – this option will only change the cell values and not affect any cell formatting in place. Note that this can cause problems if the format of the cell does not match the data (numeric versus text versus date).
Overwrite (do not clear) – do not clear any of the worksheet. The results overwrite the existing cell values (formatting is not affected). Note that if there is no new data to write, the existing data will not be cleared.
Do not include header – check this to prevent the column names from being included in the first row of the results written.
Append to Worksheet – add to the existing worksheet data.
Keep both – When appending to the existing data in the worksheet, simply append all the new data to the existing data. This may results in duplicate rows.
Do not change existing rows – If the new data contains rows with matching text and date columns, only append rows with new combinations. This does not change the previous reported values.
Update to new values – If the new data contains rows with matching text and date columns, replace the existing numeric values with the new values. This will refresh the previously captured values.
Updated for v10.0.6