How To Use The Calculate Block
The Calculation Block allows you to:
- Perform column based calculations
- Add or remove columns from the dataset, including calculated columns
- Rename or re-order columns
- Change the data type of columns
1. After adding a data source to the Canvas, drag and drop the Calculate block onto the Canvas. The block will initially appear in blue before data is passed into it.
2. Click and hold on the output node of any block, then drag and release on the input node of the Calculate block to send data into the Calculate block.
3. Click in the center of the Calculate block to select it. You will see a list of all the columns from the connected block in the bottom of the screen in the block control window.
4. The following controls are available on the calculation block:
- click and drag on the [+] symbol to the left of the column name to change the order of the columns in the resulting table
- click on the icons at the end of the row to edit or delete a column.
- click on a column name to rename the column
- click on an expression to edit the expression of a column. The default expression for a column is the result of that column from the preceding block and is labelled "Original.columnName".
5. Above the table, you have four buttons:
Add Input Columns - all inputs from a preceding block are included in the calculate block by default. If a column has been added to the preceding block after the calculation block was connected, click the "Add Input Columns" button to see and select from the list of columns sent into the block. Columns added and displayed in the list of columns in the main block control will be sent through the output node of the block
Add Calculated field - opens a modal control window for creating a calculated column using an expression (see next item);
Delete All Columns - deletes all columns from the table. Add at least one column for data to pass through the block
Set to Source - resets the block to include only the columns from the block it is connected to. All expressions will return to their original expressions.
6. When you click on “Add Calculated Field,” you will see the pop-up below. Provide a name for the column and enter an expression in the calculation field.
In the “Function Reference” column, you will see a list of all the functions you can use. Use the search field to locate the expression you’re looking for, or navigate to the function by scrolling through the list of function categories.
Click the arrow to the left of the function category name to expand the list of functions. The expression editor supports a variety of functions used in Google Sheets, Excel, Analytics Canvas, and Google BigQuery. The icon next to the name of the function denotes the source of that function.
If you can’t find a function you need, request it by clicking on the “Request a function” link.
Once you find the function you’re looking for, click on it. When you do, the structure of the function will appear above the search bar. Double-click on the function to add it to the Expression Window above.
7. In the “Columns” section, you have data from the preceding source block. Search for the column you want to use either by using the search bar or by scrolling to it. Click on the name of the column to add it to the Expression Window.
8. Make sure that you follow the structure of the function. In our example below, we manually added the second parameter. When you’re finished, click the “Validate” button. If something is not correct, you will see an error; otherwise, you will see the generated SQL.
9. To change the data type of a column, enter an expression that changes the data type. For example, using the BigQueryCAST expression: BigQueryCAST([Original.date], "date") casts a TimeStamp column to a Date column.
If you need assistance with any given function, reach out to support@analyticscanvas.com with a small sample of the raw data and a sample of what the data should look like after the transformation. We'll get back to you within 1 business day.