Query Table is a feature that enables you to prepare data for easy reporting and analysis. You can combine data from one or more tables in a reporting database and create specific data views using the standard SQL SELECT queries. These data views are similar to tables and you can perform operations such as report creation, sharing, and even creating another Query Table over an existing Query Table.

You can create Query Tables for filtering datasets, batching datasets together (union), transforming data, applying SQL query functions, joining datasets, and more.

General

1. What is a Query Table?
2. Is there any specific consideration for query table creation?
3. What is the SQL SELECT command and how is it used in Query Tables?
4. What are the SQL dialects supported in Query Tables?
5. Do you recommend any specific SQL dialect?

Creating a Query Table

1. How can I create a Query Table?
2. What are the standard SQL functions supported in Zoho Analytics?
3. How can I modify an existing query?
4. How can I rename columns in the query table?
5. Can I merge data sets using a Query Table?
6. Can I join one or more tables using a Query Table?
7. What are the types of SQL Joins supported by Zoho Analytics?
8. Can I link two Query Tables using a Lookup Column?
9. Can I create a Query Table over a Query Table?
10. How many levels of Query Tables can I create over an existing Query Table?
11. Can I create an aggregate formula for a Query Table?
12. How can I change the data type of a column in a Query Table?
13. How do I format a column?
14. Can I create co-related sub-queries?

Reporting

Working with Query Tables

1. How do I search for a value in a Query Table?
2. How do I sort a column?
3. How do I filter the data in a Query Table?
4. How can I show/hide the columns in a Query Table?
5. How can I freeze columns in a Query Table?
6. How do I re-order or resize a column?
7. How do I apply conditional formatting in a Query Table?

Collaboration

1. How do I share a Query Table?
2. How do I export a Query Table?
3. How do I embed/generate the URL of a Query Table?

Troubleshooting Tips

1. Are there any specific points that I must keep in mind while creating a Query Table?
2. I created a Query Table but it keeps loading when I access it. Why?
3. I created a query table but it timed out when I tried to Save/Access it. What should I do?
4. I was trying to remove a column from an existing Query Table. But, it throws an error. Why?

General

 1. What is a Query Table?

Query Table is a feature that enables you to prepare data for easy reporting and analysis. You can combine data from one or more tables in a database to facilitate easy reporting. These data views are similar to tables and you can perform operations such as report creation, sharing, and even creating another Query Table over an existing Query Table.

You can create Query Tables for filtering datasets, batching datasets together (union), transforming data, applying SQL query functions, joining datasets, and more.

2. Is there any specific consideration for query table creation?

Query tables facilitate advanced reporting, but improper usage might affect SQL performance.

Follow these best practices when working with query tables:

• Keep your queries simple; avoid complex formats.
• Refrain from using SELECT * to copy entire tables. Select only the required columns.
• When creating query tables, ensure proper join conditions are in place. Duplicate values in join columns can lead to inaccurate results and degrade server performance.
• Avoid using Cartesian joins (n*n). Instead, aim for 1*1, 1*n, or n*1 relationships while joining the tables.
• A GROUP BY clause should only be used for non-aggregate columns.
• Apply the GROUP BY clause to the non-aggregate columns in your query whenever aggregate functions (e.g., min(), max(), sum(), count()) are used on metric columns.
• Avoid using too many columns in a GROUP BY clause (e.g., 30 or 40 columns). Select only the necessary columns. If you need more columns, use a subquery for metric calculation, so the computation is done only on the required data and the rest is appended to the corresponding row without impacting performance.
• Alias names cannot be used in a HAVING clause.
• Apply necessary filters in the initial query to retrieve only the required data before computing aggregate values. For example, if you have 10 years of data but need analysis based on the current year, filter the current year's data to avoid calculations over the entire dataset.
• Generally, using DISTINCT on a large dataset can slow down performance. Use it only when necessary.
• Avoid using both GROUP BY and DISTINCT in a single SELECT query statement.
• It's recommended to use the UNION ALL operator instead of UNION, as UNION implicitly applies a DISTINCT operation.
• Perform computations on a smaller number of records.
  • Aggregate the dataset first, then apply the join clause.
  • Avoid using the UNION clause for referring to the same tables; use CASE WHEN statements instead.
• Prefer the IN clause over multiple OR conditions for expressions with the same left-side expression.
  • For example, instead of writing f(column) = '1' OR f(column) = '2' OR f(column) = '3', use f(column) IN ('1', '2', '3').

Avoid creating query tables for the following scenarios:

• Simple Calculations: Instead of creating a query table for simple calculations, especially over a single table, use formulas or calculations directly.
• Joining Tables: Do not create a query table solely for joining data. Zoho Analytics offers an Auto-Join feature for establishing relational data models. Use a query table only when necessary.
• Filtering Data: Instead of creating multiple query tables to share specific data for different users, apply dynamic share filter criteria.
• Multiple Use Cases: Avoid creating separate query tables for each use case. Consolidate where possible; for instance, calculate multiple metrics across the same dimensions within a single query table. Having too many query tables in a workspace can impact system performance.
• Nested Query Tables: While Zoho Analytics supports creating query tables over other query tables at multiple levels, refrain from doing this unnecessarily.
• Cleanup Unused Tables: Periodically clean up unused query tables to prevent unnecessary constraints on system resources.

 3. What is the SQL SELECT command and how is it used in Zoho Analytics?

SQL (Structured Query Language) is a standard & popular language for storing, manipulating, and retrieving data in databases (eg., Oracle, SQL Server, MySQL, etc.,).

Zoho Analytics uses the SQL "SELECT" statement for creating a Query Table. The SELECT statement is used to select data from the tables. A simple SQL SELECT query looks as shown below:

SELECT Customer Name, City FROM Customers;

This query fetches the Customer Name and City from the table Customers.

To learn more about SQL SELECT queries refer to this link.

 4. What are the SQL dialects supported in Query Tables?

Zoho Analytics currently supports SQL SELECT queries written in ANSI, Oracle, SQL Server, IBM DB2, MySQL, Sybase, Informix, and PostgreSQL SQL dialects.

Although we support all of the above-mentioned dialects, we would recommend you use the ANSI SQL dialect for better coverage and support.

 5. Do you recommend any specific SQL dialect?

We support SQL Select queries written in all of the above-mentioned dialects (Refer to Question 3). But, we would recommend you use the ANSI SQL dialect for better coverage and support.

Creating a Query Table

 1. How can I create a Query Table?

Follow the instructions below to create a new query table. In this example, we will be combining sales data from the "Sales data" table and customer details from the "Customer data" table. 

• Click the Create icon on the side panel, and select Query Table under the Create New Table section.

• The SQL query editor appears. You can now enter your SQL query to create a new query table. To insert columns, select the Tables/Columns tab. You can alternatively type column names in the editor if you know the column/field names in these tables. 

• Select the SQL Functions tab to insert the functions of your choice. Zoho Analytics has several in-built SQL functions such as Logical, Aggregate, Tabular, String, Mathematical, Date, Duration, and Business functions, that can be utilized while constructing the query table.

• Once you have entered your SQL query, click Execute Query to create your query table.
• Once your query table is ready, ensure you Save it before using it to create reports and dashboards.

 2. What are the standard SQL functions supported in a Query Table?

Zoho Analytics allows you to use all the functions that are listed under the Insert SQL Functions tab while creating a Query Table. Please do note that this is just a suggested list of functions and is not limited to it. Although, the suggested list is guaranteed to work.

 3. How can I modify an existing query?

You can modify an existing query by following the below steps:

• Open the corresponding Query Table in the Edit Design mode.

• You can either make the changes in the existing query or click the Clear Query button to clear the entire query and type a new one.
• Click Execute Query after you have finished rewriting/modifying the query.
• Click Save to save the Query Table values with the new query results.

4. How can I rename columns in the query table?

Zoho Analytics allows you to rename any column in the query table using an alias name even if the columns are referenced by other views in the workspace.

For example, let's consider a scenario where we use the query table to segregate help desk tickets based on their status, such as incoming and closed, and add alias names for better readability.

 5. Can I merge data sets using a Query Table?

You can merge data sets using the "UNION" function in a Query Table. In the below Query Table, we are combining the Product Name and License Cost from the Product Table with the Product Name and License Cost from the Sales Table.

Click here to learn more.

 6. Can I join one or more tables using a Query Table?

Yes, you can. But, we strongly recommend you use the Auto-Join feature in case you wish to join (combine) two or more tables. This feature automatically joins tables when creating reports, if the tables are connected using a Lookup column. Click to learn more.

If you would still prefer to use a Query Table to join tables, you can do so. Zoho Analytics supports the following joins:

• Left Join
• Right Join
• Inner Join

In the below example, we are using a LEFT Join to combine the column DepName from the Department Table along with the columns Emp_Name, and Joining_Date from the Employee table.

 7. What are the types of SQL Joins supported by Zoho Analytics?

Zoho Analytics supports the following joins:

• Left Join
• Right Join
• Inner Join

 8. Can I link two Query Tables using a Lookup Column?

Yes, you can link two Query Tables using a Lookup column as you do over a table.

To do so,

• Open the Query table
• Select the column that you wish to change as a Lookup column
• Right-click the column name and select Change to Lookup Column

• In the Change to Lookup column dialog that opens, select the Column to Lookup
• Click OK

 9. Can I create a Query Table over a Query Table?

Yes, you can create Query Tables over an existing Query Table. You can create a maximum of 3 levels of queries over an existing Query Table.

 10. How many levels of Query Tables can you create over an existing Query Table?

You can create a maximum of 3 levels of queries over an existing Query Table.

 11. Can I create an aggregate formula for a Query Table?

Yes, Zoho Analytics supports aggregate formulas for Query Tables. Please refer to this help document to learn more about creating aggregate formulas.

 12. How can I change the data type of a column in a Query Table?

To change the data type of the column in a Query Table, follow the below steps:

• Select the column and click the Column Properties button in the Toolbar.
• Click the Change Datatype menu option.

• In the Change Data Type dialog that opens change the data type accordingly and click OK.

 13. How do I format a column?

Zoho Analytics offers options to change the format of a column in a Query Table (such as alignment, decimal places, date formats, currency symbol, etc) depending on its data type as you can do over a table.

To format a column:

• Select the column you want to format by clicking on the header.
• Click the Format > Format Column option from the toolbar, or right-click the column name and select the Format Column option from the pop-up menu. The Format Column dialog box with available options for formatting the column will open.
• Select the preferred formatting options in the dialog box and click OK.

Formatting options provided in the dialog box differ based on the data type of the selected column. Refer to this help documentation to learn more.

 14. Can I create co-related sub-queries?

Zoho Analytics at present does not allow you to create co-related sub-queries (sub-queries inside the Where clause) . In case you have a special case where you need to use a sub query, please do mail us your requirements to onprem-support@zohoanalytics.com, we will analyze your requirement and provide you with an alternate solution.

Reporting

 1. Can I create reports over a Query Table?

Yes, you can. Query Table when created acts just like a table. You can create any type of report as you do over a table. Refer to the following documents to learn about creating reports and dashboards:

• Creating Reports
• Creating Dashboards

Working with Query Tables

 1. How do I search for a value in a Query Table?

Zoho Analytics allows you to quickly search for specific records within a large set of data. The Search box in the toolbar can be used to locate records in a Query Table that matches the keyword that you specify.

 2. How do I sort a column?

Zoho Analytics allows you to rearrange the rows in a Query Table by sorting values in columns. To sort a column follow the below steps:

Select the column and click the Sort button in the toolbar. The available sort options are:

1. Sort Ascending sorts text data in ascending alphabetical order (A to Z), numbers from smallest to largest (0-9), and dates from oldest to most recent.
2. Sort Descending sorts text data in descending alphabetical order (Z to A), numbers from largest to smallest (9-0), and dates from most recent to oldest.
3. Remove Sorting clears the applied sorting.

 3. How do I filter the data in a Query Table?

Zoho Analytics provides a Filter option to easily filter the records in your Query Table based on the criteria that you specify. Depending on the data type of the column, Zoho Analytics offers various filtering options such as filter based on specific numeric ranges, date ranges, individual values, partial matches, and more. You can also apply filters on multiple columns at a time.

To apply a filter:

• Click on the Filter button in the toolbar and select the filter that you wish to apply for each column from the dropdown as shown below.

• Click Apply to apply the filter and Save to save the filter with a name.

 4. How can I show/hide the columns in a Query Table?

To show or hide columns in a Query Table:

• Click the More button in the toolbar and select Show/Hide Columns.

• Select or unselect checkboxes in the Show/Hide/Reorder Column dialogue box to show or hide individual columns.
• You can also reorder the columns in this dialog, to reorder - select the column and click on the up or down arrow.
• Click OK.

 5. How can I freeze columns in a Query Table?

Zoho Analytics has a Freeze Column option that makes sure certain columns stay visible in the Query Table, even when you scroll horizontally across the screen. Refer to the Freeze Columns topic to learn more.

To freeze a column:

• Select the column and click More > Freeze Column.
• To unfreeze the column, click the pin icon in the top-right corner.

 6. How do I re-order or resize a column?

Zoho Analytics allows you to reorder or resize the columns in a Query Table by dragging the column as you can do in a table.

You can also do this by selecting More > Show/Hide Columns.

 7. How do I apply conditional formatting in a Query Table?

The conditional formatting feature allows you to highlight cells in a column with different backgrounds and font colors based on a condition. You must specify the required conditions/criteria for formatting. When data in a cell meets the condition, Zoho Analytics applies the corresponding formatting style that you have specified.

To apply conditional formatting:

• Select the column to format by clicking on the header.
• Click Column Properties > Conditional Formatting option from the toolbar. The Conditional Formatting dialog will open.
• Specify the conditions to format the column. (Refer to the topic "Conditional Formatting" to learn more).
• Click OK. All cells that meet the condition will be formatted accordingly.

This is similar to the conditional formatting feature in a table. To learn more refer to this link.

Sharing and Collaboration

 1. How do I share a Query Table?

You can easily share the Query Tables that you create with other users using the Share option. The Share option in a Query Table is similar to that in a table. Once you share your Query Table your users will be able to create reports and dashboards over the same.

Refer to the Sharing and Collaboration help page for more details on this.

 2. How do I export a Query Table?

Zoho Analytics allows you to export the Query Table that you have created into various file formats like CSV, PDF, XLS, or HTML. Refer to this document to learn more.

 3. How do I embed/generate the URL of a Query Table?

Zoho Analytics allows you to publish the Query Table that you have created on your websites or blogs. You can also set and control the access privileges for the users who access the Query Table. This option is enabled only for database owners or administrators.

Refer to the Publishing Options topic to learn how to publish a Query Table.

Troubleshooting Tips

 1. Are there any specific points that I must keep in mind while creating a Query Table?

Yes, please make sure that your Query Table adheres to the following points

Performance Considerations

• Avoid complex queries
• Avoid unnecessary joins. In case you wish to join two or more tables, we suggest you use the Auto-Join feature
• Avoid cartesian joins
• Avoid creating a Query Table over a Query Table as much as possible

Functional Considerations

• Use Group By clause whenever the Aggregate functions (min(),max(),sum(),count(),...etc.,) and columns are used together
• Non aggregate columns present in SELECT columns should be used in the Group By clause
• Alias names can not be used in HAVING clause

 2. I created a Query Table but it keeps loading when I access it. Why?

Query Tables are performance intensive. The performance of the Query Table depends on the number of rows, the types of joins used, the functions used, etc. Please make sure that you adhere to the points mentioned in the previous question while creating a Query Table.

We recommend you keep the query simple, if the issue persists try restructuring the query used in the Query Table. For further assistance, you can also write to us at onprem-support@zohoanalytics.com. We will help you optimize your Query Table.

 3. I created a query table but it timed out when I tried to Save/Access it. What should I do?

As said in the previous question, Query Tables are performance-intensive. Please make sure that you keep the points in Question#1 in mind while creating a Query Table.

For further assistance, you can also write to us at onprem-support@zohoanalytics.com. We will help you optimize your Query Table.

 4. I was trying to remove a column from an existing Query Table. But, it throws an error. Why?

This can happen when the column that you are trying to delete from the Query Table has dependent reports created over it. Please edit the dependent report to remove the column that you are trying to delete or delete the report itself. Once all the dependencies are removed, you will be able to delete the column.