Calculated Columns

Creating expressions

To create an expression when adding a calculated column, enter the expression in the Expression field, or build a new expression.

You can also use the advanced mode to add calculated columns to your queries. For more information, see Customizing Administrator Preferences: Setting your warehouse preferences or Customizing User Preferences: Setting your warehouse preferences.

To build an expression

  1. Select the data type. There are five available data types:

    After you select a data type, all of the available columns that fall within that data type appear in the Columns list.
  2. Select a column name, enter a constant, and/or create an open-ended expression.

  3. Select an operator. In the Operators list, click on the operator you want to use.

    If you selected the open-ended parenthesis in the previous step, you will need to select ")" to close the parentheses in this step.

  4. Apply a function to your column.

    Data Type

    Function

    Description

    Character Substring Subsets a single column or an entire expression. When you select Substring, you will be prompted to enter the starting position of the substring and the number of characters you wish to subset. If you leave the number of characters blank, all characters from the starting position will be displayed.
    Strip Trailing Blanks Removes any blank spaces from the end of a line of character data.
    Left Justify Left justifies character data.
    Right Justify Right justifies character data.
    Upper Case Converts all character data into upper case.
    Lower Case Converts all character data into lower case.
    Numeric Absolute Value Returns the absolute value of the number.
    Floor Rounds down to the nearest whole number.
    Ceiling Rounds up to the nearest whole number.
    Round Rounds to the nearest whole number.
    Weekday Retrieves the weekday from the numeric date.
    Day Retrieves the day from the numeric date.
    Month Retrieves the month from the numeric date.
    Year Retrieves the year from the numeric date.
    Date/Time Date Part Retrieves the date from the date/time data type.
    Time Part Retrieves the time from the date/time data type.
  5. Select
You can also click Undo Last to clear the last item you entered in the Expression field, or Clear to clear the expression you are building.