Query Tables for Data Preparation

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 Workspace 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, exporting, 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.

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, exporting, 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.

2. What is SQL SELECT command and how is it used in the Zoho Advanced 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 Advanced Analytics app 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.

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

Zoho Advanced Analytics app 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.

4. Do you recommend any specific SQL dialect?

We support SQL Select queries written in 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.

Creating a Query Table

1. How can I create a Query Table?

You can create a query table in Zoho Advanced Analytics by following the below steps:

From your homepage, click Create > Query Table.
You will get navigated to Query Edit Mode. You can create your query table by typing your query in the stage area, Insert Columns, Insert Functions.
Preview the create query using the Execute Query button.
Once you are done, click Save.
Click View Mode to view the created query table.

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

Yes, please do 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 to 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 Group By clause
Alias names can not be used in HAVING clause

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

Zoho Advanced Analytics app 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.

4. How can I modify an existing query?

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

Open the corresponding Query Table and click the Edit Design button.
The query table will open in edit 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.

5. I have a dimension column with multiple repetitive values, (e.g., Product Category, Region, Department Name, etc). Can I have each unique row value in the column transformed to a separate column in a new table?

Yes, you can split the distinct values from a column and transform them into multiple columns using the PIVOT clause.

The PIVOT keyword rotates rows into columns. It transforms the input table in a query in such a way that each unique value in a chosen column is converted to a separate column in the output table.

Example

In this case, the Sales data across Product Category is in a single column.

Using the below query you can split the unique value of the column Product Category into multiple columns as Grocery, Furniture, and Stationery.

SELECT

"Pivottable"."State" as "State",

"Pivottable"."Grocery" as "Grocery",

"Pivottable"."Furniture" as "Furniture",

"Pivottable"."Stationery" as "Stationery"

FROM /* Fetches the needed column into a temporary table (not visible for users)*/ ( SELECT

"Sales Region"."State" as "State",

"Sales Region"."Product Category",

"Sales Region"."Sales"

FROM "Sales Region"

) /* split the unique values of a row into columns and assign an aggregate column to get the value for the columns*/ AS "pivot-source"
pivot
(sum("pivot-source"."Sales") FOR "pivot-source"."Product category" in ( "Grocery", "Furniture", "Stationery" )
) AS Pivottable

6. I have all my regional sales as different columns. Can I merge the data into a single column?

Yes, you can merge the data from multiple columns into a single column using the UNPIVOT clause.

The UNPIVOT keyword rotates columns into rows. It transforms the chosen columns in the input table into a single column's row values in the output table.

Example

In this case, the Sales data for each region is in a column.

Using the below query, the columns East, West, South, and Central are combined into a column named Region.

SELECT UnPiovtTable.*

FROM ( SELECT

"Central",

"East",

"South",

"West",

"Date of Date"

FROM "Regional Sales"

) /* Combines the columns East, West, South, and Central into a column named Region */

AS Temp

unpivot

("Sales" FOR "Region" IN (Temp."East", Temp."South", Temp."West", Temp."Central")

) AS UnPivotTable

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

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 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.

If you would still prefer to use a Query Table to join tables, you can do so. Zoho Advanced Analytics app 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.

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

Zoho Advanced Analytics app supports the following joins:

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 Cardinality and 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, the Zoho Advanced Analytics app 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 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 Advanced Analytics app 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 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.

14. Can I create co-related subqueries?

Zoho Advanced Analytics app at present does not allow you to create co-related subqueries (subqueries inside the Where clause). In case you have a special case where you need to use a subquery, please do mail us your requirements to support@zohoanalytics.com, we will analyze your requirement 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:

Working with Query Tables

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

Zoho Advanced Analytics app 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 Advanced Analytics app 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:

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.
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.
Remove Sorting clears the applied sorting.

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

Zoho Advanced Analytics app 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, the Zoho Advanced Analytics app 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:

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.

Note: If you wish to permanently filter out the data in your Query Table, use the WHERE clause in your query.

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 Advanced Analytics app has a Freeze Column option that makes sure certain columns stay visible in the Query Table, even when you scroll horizontally across the screen.

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 of the column.

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

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

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 Advanced Analytics app applies the corresponding formatting style that you have specified.

To apply conditional formatting:

Right-click the column and click Column Properties > Conditional Formatting option from the toolbar. The Conditional Formatting dialog will open.
Specify the conditions to format the column.
Click OK. All cells that meet the condition will be formatted accordingly.

Troubleshooting Tips

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

Yes, please do 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 to 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 Group By clause
Alias names cannot 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 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 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?

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 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 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.