Search data error reference

This reference identifies the error codes that can appear when you use Search data in ThoughtSpot, including the cause and suggested resolution.

Search data error codes

11035

Description

Unsupported query: unique count and a rank-related function coming from the same fact table.

Details

last_value is a ThoughtSpot formula function introduced in 9.12.0.cl . This function is designed to provide analysts the capabilities to create formulas for semi-additive measures. An example could be inventory balances.

For example: "How much stock does Acme Corp maintain for each Region?"

A secondary metric could be Average Stock on Hand. A common definition for this is: balance of stock divided by the unique count of locations.

In ThoughtSpot this formula could be defined as:

last_value(sum(balance),query_groups(),{transaction date}) / unique count(location id)

An alternative example could be a user asking the question as separate measures. For example: "Show me the balance and unique count of locations for each region".

In this scenario the search would return two measure columns: Balance & Unique Count Locations.

Cause

In the example defined, the last value aggregate column is ‘balance’ and the unique count column is ‘location id’. Error 11035 is returned when the unique count column, ‘location id’, is derived from either the same fact table as balance or as a dimensional table that is joined to the fact table.

Resolution

Unique count can be combined with last_value across a chasm trap query. Therefore pending resolution for this limitation an analyst can leverage group_aggregate to force a chasm trap query.

For example: The group_aggregate definition ensures a chasm trap is generated and the inclusion of query_groups()+{} ensures that this query plan is not optimized.

group_aggregate(unique count(location id), query_groups()+{}, query_filters())

11036

Description

Unsupported query: nested aggregate function, for example, fx = max(avg(c1)).

Details

This error is displayed in the formula editor when an aggregate formula is defined directly within another aggregate formula, for example, average(sum(sales)). Note that this error may be displayed when an aggregate is wrapped around a column, such as average(total sales). In this scenario, the worksheet column would be defined with an aggregate formula, such as total sales = sum(sales).

Cause

can’t use an aggregate function within an aggregate function.

Resolution

To resolve this, group functions should be used to force the query generation to create a CTE query plan. That is, assume the business requirement is to return the average daily sales for each product. the formula would be defined as the following:

average daily sales = average(group_aggregate(sum(sales),query_groups()+{date(transactionDate)}, query_filters()))

Where the following logic is applied

group_aggregate(sum(sales),query_groups()+{date(transactionDate)}, query_filters()) → calculate the daily sum of sales.

average() → calculate the average of the daily sales for the level of detail of the search.

The resulting query would be: [Product] [Average Daily Sales].

11037

Description

Formulas that mix aggregates and non-aggregate expressions are not supported.

Details

This error is displayed in the formula editor when a formula incorrectly includes a combination of an aggregate expression and a non-aggregate expression (non-group-by).

The following is an example of a formula that results in an error. Where

  • region='apac' is the non-aggregate

  • sum(sales) is an aggregate

fx = if(region='apac' then sum(sales) else 0.

Cause

Historically these formulas could be written in ThoughtSpot. However, these often resulted in SQL generation queries. In the example above, if the region column is not included in the search, then a group by sql query would be returned. This is because the:

  • aggregate function is calculated across all rows of data : sum(sales).

  • non-aggregate is a calculation at the row level : region='apac'.

Resolution

The formula must be re-written with the following as examples:

Option 1: wrap the non-aggregate column in an aggregate function.

fx = if(max(region)='apac' then sum(sales) else 0

Option 2: re-write the function as an aggregate if formula.

fx = sum_if(region='apac', sales)