This article focuses on showing how to create a derived table for a data source in Lambda Analytics
A derived table is an additional query whose result becomes another set of items available in the data source. You can use the items in a derived table for other operations on the data source, such as joining tables, defining a calculated field, or filtering. The items in a derived table can also be referenced in the data source's security file and locale bundles.
Creating a Derived Table
You can create a derived table in a data source by first building a custom query using tables that have been selected. You must first run the query, and then select columns from the query result to use in the data source design.
The clauses in a query determine the structure and contents of the table returned by the query. For example, the 'where' clause may contain conditions that determine the rows of the derived table.
To define a derived table:
1. From the Lambda Analytics homepage, click View Existing under the Data Sources link on the top navigation bar.
2. Find your desired data source in the Repository, right-click the corresponding title, and select Edit from the context menu.
3. Under the Data Structure panel, hover over the Add icon and select 'Create Derived Table...'
4. In the Query ID field, enter an appropriate name for your table.
5. In the Query field, enter a valid SQL query. The query may refer to any table or column in the Available Objects panel. Only queries that begin with the select statement are allowed. Do not include a closing semi-colon (;).
6. Expand the tables in the Available Objects panel. Double-click the column names to add them to the query.
7. When the query is complete, click Run Query to test it and choose the list of columns from the results.
8. By default, all columns in the query results are selected. You can change the selection through using
9. Click Save Table to add the derived table to the Available Objects panel. You can now include it into your data source.
Listed here is an ever-growing collection of case studies focused on making use of the many features available while creating derived tables. Each case study has a specified outcome, a set of steps to follow, and an example created in full detail from start to finish.