Create a computed column

A computed column is one of the most useful analytical tools in the 1010data Insights Platform. Use a computed column to add a column of information to a worksheet typically using the information from one or more existing columns.

Computed columns are columns you create yourself in many cases using information that is already in the table with which you are working. For example, suppose you have a table that contains all the sales data for a chain of stores. This table might have one column called Sales, which is the purchase price paid by the customer. It might have another column called Cost, which is the cost of the item to the retailer. With a computed column, it is easy to create a third column called Margin that contains the difference between the sale price and the cost.

In this tutorial, you will use the Sales Item Detail table in the Trillion-Row Spreadsheet (TRS) to find the margin using a computed column.

To create a computed column:

  1. From the menu bar, select Tools > Trillion-Row Spreadsheet.
    The Insights Platform displays the TRS window.

  2. Open the Sales Item Detail table (pub.demo.retail.item) by completing the following:
    1. In the Analyze tab, enter pub.demo.retail.item in the Search field.

      In this example, you were given the table name (pub.demo.retail.item). If you did not know the name, you could perform a search by entering the table title (Sales Item Detail) instead.

    2. Press Enter.
      TRS displays the results of your search.

    3. Click Sales Item Detail.
    TRS displays the New operation panel in the analysis pane on the left and the Sales Item Detail table in the results pane on the right.

    In this tutorial, the Sales and Cost columns, outlined in red above, are used to create the Margin computed column.

  3. In the New operation panel, click Compute.
    TRS displays the Computed column panel.

  4. Complete the following fields:
    Name Enter margin.

    This is the name the Insights Platform uses to interact with the column. It is also used when writing more advanced expressions and queries. The column name may only contain alphanumeric characters or underscores and must begin with an alphabetic character (e.g., percent_total_sales). It may not contain any spaces or other special characters.

    This is a required field.

    Label Enter Margin.

    This is the column heading that displays by default at the top of a column in the user interface. The column label may contain any combination of uppercase and lowercase letters, numbers, spaces, and special characters. If you want to have a multi-line column label, use the backtick character (`) to separate the lines (e.g., "Percentage`Total Sales (%)").

    While not required, this field is recommended.

Next, you will enter an expression to find the margin. An expression is a calculation you create to help you analyze your data. In this case, you want to subtract the value in the Cost column from the value in the Sales column.

  1. In the expression field, enter the following: sales-cost

Next you will define the format of the computed column. You can configure items such as the display format, column width, and the number of decimal places to display.

  1. Click the More Options switch.
    TRS displays the formatting options.

  2. Complete the following:
    Display Format Select Number: 1,234,567.89.

    This drop-down list instructs the Insights Platform how to display the new number in the computed column. For example, as a date, a number, as text, and so forth.

    Column Width Select 5.

    This option determines how many place values to hold in the column. In other words, the width of the column by the number of characters.

    Decimal Places Select 2.

    This option sets how many numbers are displayed after the decimal point.

    In this tutorial, dollar amounts are used. Therefore, two decimal places are usually the appropriate setting. However, you may find that there are situations where you need more or none at all.

  3. Click the Submit operation () icon.
    TRS creates the Margin computed column.

  4. When you are finished with this tutorial, close the TRS window.

You just created a new column in the worksheet with a very small amount of effort. The values in each row are calculated by the equation you entered as the expression, and you can now easily see the margin for every item sold in the entire worksheet. Of course, computed columns are much more powerful than merely subtracting or adding one column from another. With a little practice, you will be able to build columns based on more complicated equations, such as standard deviations and cumulative frequency distributions.

To learn more about computed columns and value expressions, see Computed Columns in the 1010data Insights Platform User's Guide.