Query tables
Analytics Plus offers a way to create tables by combining one or more tables in a workspace or creating specific data views using the standard SQL SELECT queries. These data views are similar to tables and let you create reports, share the data, 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
- What is a query table?
- What is the SQL SELECT command, and how is it used in query tables?
- What are the SQL dialects supported in query tables?
Creating a query table
- How can I create a query table?
- Is there any specific consideration for query table creation?
- What are the standard SQL functions supported in Analytics Plus?
- How can I modify an existing query in the query table?
- Can I merge data sets using a query table?
- Can I join one or more tables using a query table?
- Can I link two query tables using a lookup column?
- Is it possible to create a query table over a query table?
- Is it possible to create a query table over a query table?
- What is the list of operations performed over a query table?
Troubleshooting Tips
- Why does the query table keep loading or being timed out when I access it?
- How to resolve the error that occurs when removing a column from an existing query table?
- How to transform each unique row value in a dimension column with multiple repetitive values into a separate column in a new table?
- Can I merge all the regional sales present in different columns into a single column?
General
1. What is a query table?
Query tables enable you to create tables by combining one or more tables in a workspace or creating specific data views using the standard SQL SELECT queries.
2. What is the SQL SELECT command, and how is it used in query tables?
SQL (Structured Query Language) is a standard language for storing, manipulating, and retrieving data in databases (e.g., Oracle, SQL Server, MySQL, etc.).
Analytics Plus uses the SQL SELECT statement to select data from different tables and create a query table. Click here to learn more about SQL SELECT queries.
3. What are the SQL dialects supported in query tables?
Analytics Plus allows you to create a query table using the following SQL dialects: Oracle, SQL Server, IBM DB2, MySQL, Sybase, Informix, PostgreSQL, and ANSI SQL.
Creating a query table
1. How can I create a query table?
2. Is there any specific consideration for query table creation?
Yes. While creating query tables, ensure that it adheres to the following points:
Performance Considerations
To get a better performance of your query table try to avoid the following as much as possible.
- Complex queries.
- Unnecessary joins. To join two or more tables, we suggest you use the Auto-Join feature.
- Cartesian joins.
- Creating query tables over existing query tables.
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 the HAVING clause.
3. What are the standard SQL functions supported in Analytics Plus?
Analytics Plus enables you to use all the functions listed under the SQL Functions tab to create query tables. Analytics Plus supports Logical, Aggregate, Tabular, String, Mathematical, Date, Date Arithmetic, Date Conversion, Current Date, Business Date, Duration, and other General functions.
4. How can I modify an existing query in the query table?
Analytics Plus allows you to edit the existing query used in the query tables. To do this, follow the steps below:
- Open the required query table in Edit Design mode.
- Make the required changes in the existing SQL query, or click the Clear Query button to clear the entire query and type a new one.
- Click Execute Query. The resulting table will be displayed in the Expected Result tab.
- Click Save to save your query table.
Note: You cannot edit columns that are used in generating a report in the workspace.
5. Can I merge data sets using a query table?
Yes. Analytics Plus lets you merge the common data sets available in different tables using the UNION function. For example, the below query table combines the Employee ID and Employee Name from the Projects Given table with the Employee ID and Employee Name from the Projects Submitted Date table.
6. Can I join one or more tables using a query table?
Yes. Analytics Plus allows you to join tables using one of the following joints:
The following images show the data combined using all three join types.
7. Can I link two query tables using a lookup column?
Yes. Analytics Plus lets you link two query tables using a lookup column. Click here to learn more about lookup columns. To connect query tables using the lookup column, follow the steps below.
- Open the required query table and select a column to be changed to a lookup column.
- Right-click and choose Change to Lookup Column.
- In the dialog that appears, select a column to look up from the list of tables available in the workspace.
- Click Save & Close.
8. Is it possible to create a query table over a query table?
Yes. Analytics Plus allows you to create a maximum of 3 levels of queries over the existing query table. Follow the steps below to create a new query table from an existing query table.
- Open the required workspace.
- Click the + Create button from the side panel and choose Query Table.
- Use the required columns from the existing query table and other tables in the workspace.
- Click Execute Query and Save to create a new query table.
9. Is it possible to create a query table over a query table?
Yes. Analytics Plus enables you to create an aggregate formula for query tables. Click here to learn more about creating aggregate formulas.
10. What is the list of operations performed over a query table?
Query tables work just like any other table in the workspace. Analytics Plus enables you to perform all the operations performed over a table created manually or by importing data. Refer the documentation to learn more about table operations.
Troubleshooting Tips
1. Why does the query table keep loading or being timed out when I access it?
The performance of the query table depends on the number of rows, the types of joins used, the functions used, etc., in the table. Please ensure you adhere to the points mentioned while creating a query table.
If the issue persists, try restructuring the query used in the query table, or you can also write to us at analyticsplus-support@manageengine.com.
2. How to resolve the error that occurs when removing a column from an existing query table?
Analytics Plus does not allow you to remove a column when there are dependent reports created using that column.
Before deleting a column in the query table, Analytics Plus performs a dependency check. If dependent views are found, Analytics Plus aborts the deletion process and displays the dependent items for you to review. You can review and delete the dependent items to resolve the issue.
3. How to transform each unique row value in a dimension column with multiple repetitive values into a separate column in a new table?
Analytics Plus enables you to split the distinct values in a column and transform them into multiple columns using the PIVOT clause.
The PIVOT keyword rotates rows into columns and transforms each unique value in a chosen column in the input table to a separate column in the output table.
For example, let us split the Sales data across the single column Product Category.
The unique value in the Product Category column is split into multiple columns, such as Grocery, Furniture, and Stationery, and the Sales data is displayed under the corresponding Product Category column.
4. Can I merge all the regional sales present in different columns into a single column?
Yes. Analytics Plus allows you to merge the data from multiple columns into a single column using the UNPIVOT clause.
The UNPIVOT keyword rotates columns into rows and transforms the chosen columns in the input table into single column row values in the output table.
For example, let's merge the Sales data from multiple Region columns in the below table.
The columns East, West, South, and Central are combined into a column named Region, and the Sales data from multiple Region columns is displayed under the corresponding row of Sales column.
