Creating a Derived Table

This article focuses on showing how to create a derived table for a data source in Lambda Analytics


Derived Tables

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.

Back to TOPICS

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. 

important-2  NOTE

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. 

Back to TOPICS

Case Studies

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. 

Case Study: Identifying Cohort or Audience Used for Course Enrolments

Case Study: Adding Custom Seminar Fields to Data Sources

Case Study: Adding Custom User or Course Fields to Data Sources

Case Study: Adding User's Role to Data Sources

Back to TOPICS