You can create Query Tables for filtering datasets, batching datasets together (union), transforming data, applying SQL query functions, joining datasets and more.
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 create 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.
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.
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 to use the ANSI SQL dialect for better coverage and support.
We support SQL Select queries written in the all of the above-mentioned dialects (Refer Question 3). But, we would recommend you to use the ANSI SQL dialect for better coverage and support.
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 Insert columns tab. You can alternatively type column names in the editor if you know the column/field names in these tables.
Select the Insert SQL Functions tab to insert functions of your choice. Zoho Analytics has a number of 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. In our example, we are combining the email column from a customer data table with the sales data. The common column, Customer id is used to join the two tables.
Once your query table is ready, make sure you save before using it to create reports and dashboards.
Yes, please do make sure that your Query Table adheres to the following points:
Performance Considerations
Functional Considerations
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 t work.
You can modify an existing query by following the below steps:
Yes, 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 Product Table with the Product Name and License Cost from the Sales 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:
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, Joining_Date from the Employee table.
Zoho Analytics supports the following joins:
Yes, you can link two Query Tables using a Lookup column as you do over a table.
To do so,
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.
You can create a maximum of 3 levels of queries over an existing Query Table.
Yes, Zoho Analytics supports aggregate formulas for Query Tables. Please refer to this help document to learn more about creating aggregate formulas.
To change the data type of the column in a Query Table, follow the below steps:
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:
Formatting options provided in the dialog box differs based on the data type of the selected column. Refer to this help documentation to learn more.
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 provide you with an alternate solution.
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:
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.
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 tool bar. The available sort options are:
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 match, and more. You can also apply filters on multiple columns at a time.
To apply a filter:
Note:
To show or hide 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:
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 the this by selecting More > Show/Hide Columns.
The conditional formatting feature allows you to highlight cells in a column with different background 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:
This is similar to the conditional formatting feature in a table. To learn more refer this link.
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.
Note:
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.
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.
Yes, please do make sure that your Query Table adheres to the following points
Performance Considerations
Functional Considerations
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 do make sure that you adhere to the points mentioned in the previous question while creating a Query Table.
We recommend you to 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.
Like said in the previous question, Query Tables are performance intensive. Please do 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.
This can happen when the column that you are trying to delete from the Query Table has dependant reports created over it. Please do edit the dependant report to remove the column that you are trying to delete or delete the report itself. Once all the dependancies are removed, you will be able to delete the column.
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.