The query_analytics_data tool reads rows from one analytics table: a Datamart, a Data Source, a Rollup, or a table of a Model Object. You can select columns, filter and sort the rows, aggregate values grouped by columns, and join other analytics tables. The tool returns up to 500 rows per call. To find the tables and their columns, use the List Tables tool and the Get Table Fields tool.
Tool Name – query_analytics_data
Title – Query Analytics Data
Type – Private
Access Profile – Operator
Permissions
The tool is available to the Operator access profile when tool access control is on. The query_analytics_data tool uses your Pricefx permissions. Pricefx applies your permissions to every query, so the tool returns only the rows that you have permission to view.
Parameters
|
Parameter |
Required |
Description |
|---|---|---|
|
|
Yes |
The kind of analytics table: datamart, dataSource, rollup, or modelTable. |
|
|
Yes |
The first identifier of the table, as the |
|
|
Only for modelTable |
The name of the Model Object table, as the |
|
|
Yes |
The exact column names to return, as the |
|
|
No |
A list of filter conditions. Each condition is an object: Default: No filter. |
|
|
Yes, for each filter |
The column to filter on. |
|
|
Yes, for each filter |
The comparison: |
|
|
Yes, for each filter |
The comparison value. Use a non-empty list with |
|
|
No |
One column to sort the result by: one of |
|
|
No |
Maximum number of rows to return, from 1 to 500. When aggregating, the limit applies to the number of groups. The tool changes a value below 1 to 1 and a value above 500 to 500. Default: 50. |
|
|
No |
A list of aggregations, grouped by |
|
|
Yes, except for |
The column to aggregate. The column must not also be in |
|
|
Yes, for each aggregate |
The aggregation method: |
|
|
No |
The name of the result column. Default: |
|
|
No |
A list of other analytics tables to join, applied in the listed order. Default: No join. |
|
|
Yes, for each join |
The kind of the joined table: datamart, dataSource, rollup, or modelTable. |
|
|
Yes, for each join |
The first identifier of the joined table. |
|
|
Only for modelTable |
The second identifier of the joined table. |
|
|
Yes, for each join |
INNER keeps only rows with a match. LEFT_OUTER keeps all rows and leaves the joined columns empty when there is no match. |
|
|
Yes, for each join |
One or more |
|
|
Yes, for each join |
The columns of the joined table to add to the result. Each entry is an object: |
Output
The query_analytics_data tool returns an object with the following fields.
|
Field |
Description |
|---|---|
|
|
The kind of the queried table. |
|
|
The first identifier of the queried table. |
|
|
The second identifier of the queried table. Empty when the kind needs none. |
|
|
The columns in each row, in order. When aggregating, the group-by columns come first, followed by the aggregate result columns. |
|
|
The matching rows. Each row contains the values keyed by column name. |
|
|
The number of rows returned. |
|
|
The row limit that the tool applied, after any change to the requested value. |
|
|
The filter conditions that the tool applied. |
|
|
The aggregations that the tool applied: |
|
|
The joins that the tool applied, in order: |
|
|
true when |
Behavior and Limitations
-
The
query_analytics_datatool reads one analytics table per call and can join other analytics tables to it. -
The
query_analytics_datatool joins only Datamarts, Data Sources, Rollups, and Model Object tables. To read Products, Customers, Sellers, or their extensions, use thequery_business_datatool. Analytics tables and business tables are stored in different databases and cannot be joined. -
Joins match rows by equal column values only. The tool supports INNER and LEFT_OUTER joins.
-
When one row matches several rows in a joined table, the result has one row for each match. A SUM can then count the same value more than once.
-
The tool uses a join key column that is not in
columnsfor the join, but does not return it. -
The tool applies filters after the joins and before aggregation. You cannot filter on an aggregated value. To find the top or bottom groups, sort by the aggregate result column instead.
-
The tool combines filter conditions with AND only. A condition that matches one of several values uses
inSet. -
max_rowslimits the number of rows after the joins and aggregation. -
A filter can fail when the type of the filter value does not match the type of the column. The
get_table_fieldstool shows the type of each column.
Errors
|
Situation |
Error Message |
|---|---|
|
|
|
|
An identifier is given for a kind that does not take it |
|
|
A required identifier is empty |
|
|
A |
|
|
A join has no |
|
|
Two joined columns have the same output name |
|
|
A |
|
|
The |
|
|
An |
|
|
A filter with any other operator has a list or no value |
|
|
An aggregation uses a method other than |
|
|
The same column is in both |
|
|
Two aggregations have the same result column name |
|
|
An aggregate result column name matches a name in |
|
|
Pricefx rejects the query, for example because a column does not exist or a filter value has the wrong type |
|