Creator: Creating custom questions with the notebook editor

Creator allows users to ask more advanced questions of their data with the notebook editor.

If you have a question that’s a bit more involved than a simple question, you can create a custom question using the notebook editor. You can get there by clicking the Question button in the navigation bar and selecting Custom Question. If you started from a Simple question or a saved question, you can get back to the custom question notebook editor by clicking the icon in the top-right of the screen.

➡️ The parts of the notebook

The notebook is made up of a sequence of individual steps. Under each step you’ll see buttons to add more steps after the current one. To the right of each step is a preview button that shows you the first 10 rows of the results of your question up to that step.

➡️ Picking your starting data

This first step is required, and is where you pick the data that you want to base your question on. In most cases you’ll pick one of the tables in the IDaP, but you can also choose a previously saved question’s result as the starting point for your new question.

What this means in practice is that you can do things like use complex SQL queries to create new tables that can be used as starting data in a question just like any other table in your database.

You can use most saved questions as source data, provided you have permission to view that question. You can even use questions that were saved as a chart rather than a table.

There are some kinds of saved questions that can’t be used as source data:

• questions that use Cumulative Sum or Cumulative Count aggregations
• questions that have columns that are named the same or similar thing, like Count and Count 2

➡️ Filtering

When you add a filter step, you can select one or more columns to filter on. Depending on the type of column you pick, you’ll get different options, like a calendar for date columns.

You can add subsequent filter steps after every Summarise step. This lets you do things like summarise by the count of rows per month, and then add a filter on the count column to only include rows where the count is greater than 100. (This is basically like a SQL HAVING clause.)

➡️ Filter expressions and including ORs in filters

If you have a more complex filter you're trying to express, you can pick "Custom Expression" from the add-filter menu to create a filter expression. You can use comparison operators like greater than (>) or less than (<), as well as spreadsheet-like functions.

➡️ Summarising

Adding a summarise step lets you choose how to aggregate the data from the previous step. You can pick one or more metrics, and optionally group those metrics by one or more columns. When picking your metrics you can choose from basic functions like sum, average, and count; or you can pick a common metric that an admin has defined; or you can create a custom expression by writing a formula.

If you summarise and add a grouping you can then summarise again. You can also add steps to filter and/or join in between. For example, your first summarisation step could be to get the count of orders per month, and you could then add a second summarisation step to get the average monthly order total by selecting the Average of… your count column.

➡️ Custom expressions

Custom expressions allow you to use spreadsheet-like functions and simple arithmetic within or between aggregation functions. For example, you could do

Average(sqrt[FieldX]) + Sum([FieldY]) 

or

Max(floor([FieldX] - [FieldY]))

where FieldX and FieldY are fields in the currently selected table.

➡️ Creating custom columns

Custom columns are helpful when you need to create a new column based on a calculation, such as subtracting the value of one column from another, or extracting a portion of an existing text column. Note that columns you add in a custom question are not permanently added to your table; they’ll only be present in the given question.

You can use the following math operators in your formulas: +, –, * (multiplication), and / (division), along with a whole host of spreadsheet-like functions. You can also use parentheses to clarify the order of operations.

➡️ Sorting results

The sorting step lets you pick one or more columns to sort your results by. For each column you pick, you can also choose whether to sort ascending or descending; just click the arrow to change from ascending (up arrow) to descending (down arrow).

➡️ Setting a row limit

The row limit step lets you limit how many rows you want from the previous results. When used in conjunction with sorting, this can let you do things like create a top-10 list, by first sorting by one of the columns in your result, then adding a row limit of 10. Unlike other steps, the row limit step can only be added at the end of your question.

➡️ Joining data

You can join data to combine your current data with another table, or even with a saved question.

After you click on the Join Data button to add a join step, you’ll need to pick the data that you want to join.

Next, you’ll need to pick the columns you want to join on. This means you pick a column from the first table, and a column from the second table, and the join will stitch rows together where the value from the first column is equal to the value in the second column. A very common example is to join on an ID column in each table, so if you happened to pick a table to join on where there is a foreign key relationship between the tables, Creator will automatically pick those corresponding ID columns for you. At the end of your join step, there’s a Columns button you can click to choose which columns you want to include from the joined data.

By default, Creator will do a left outer join, but you can click on the Venn diagram icon to select a different type of join. Not all databases support all types of joins, so Creator will only display the options supported by the database you’re using.

Here are the basic types of joins:

• Left outer join: select all records from Table A, along with records from Table B that meet the join condition, if any.
• Right outer join: select all records from Table B, along with records from Table A that meet the join condition, if any.
• Inner join: only select the records from Table A and B where the join condition is met.
• Full outer join: select all records from both tables, whether or not the join condition is met.

A left outer join example: If Table A is Student Classes and Table B is Classes, when you do a left outer join your results will be a full list of each of the classes every student is enrolled in, and each enrollment row will also display the columns of the class for that enrollment. Since a single student can be in many classes, a given student's enrollment information might be repeated many times for different class rows. If there isn’t a corresponding class for a given class enrollment, the class enrollment's information will be shown, but the class columns will just be blank for that row.

Multiple stages of joins

In many cases you might have tables A, B, and C, where A and B have a connection, and B and C have a connection, but A and C don’t. If you want to join A to B to C, all you have to do is add multiple join steps. Click on Join Data, join table A to table B, then click the Join Data step below that completed join block to add a second join step, and join the results of your last join to table C.