Add Column Transform

Important: The Add Column transform is not available in the Trial version. Upgrade to Professional Edition!

The Add Column transformation adds a column to the table. You give it a name and a formula for the contents, or just some text. The formula uses expressions based on the Birst Query Language, BQL. (BQL is similar to macro or other query languages.) Then you assign a data type for the column.

You don't select a column first. Use the Transforms menu for a source on the list at the left.

Example of Add Column

The Add Column transformation now utilizes an Expression Editor that offers suggestion as you type. When adding a column, you can enter your expression in the editor and Birst will prompt you with possible functions, attributes and measures.

Many times, a new column is based on one or more existing columns for which a formula can be applied. For example,

In this example, we made use of the DateDiff function to return the number of days to ship based on the columns OrderDate and ShippedDate. You provide a new column name and select the data type, then start typing in the expression editor your formula.

Birst continues to provide auto-complete suggestions until the expression is complete.

The sample code used in this example is:

DATEDIFF(DAY, [OrderDate],[ShippedDate])

And the following is the animated version of this example.

 

In next example, we demonstrate how to prepend text to the contents of an existing column to create a new column:

'Birst Ninja' + ' '+[FirstName]

Note: If you want spaces, enclose the spaces with single quotes.

Expression Editor supported Functions and Operators

On user input, auto-completion should be able to suggest the following:

• Operators (such as +, =, IS NULL)
• Functions (such as ABS, IFNULL, SUBSTRING)
• Operands

  • logical column names that were output from the previous step, enclosed in square brackets (ex. [CategoryID])

  • values like 1, true, 'string', 0.5, #12/31/2017#, #12/31/2017 11:59:59 PM#

The following functions and operators should be supported:

• Operators

  • = equals

  • * times

  • + plus

  • / divided by

  • - minus

  • % modulo

  • < less than

  • > greater than

  • <> not equal to

  • <= less than or equal to

  • >= greater than or equal to

  • AND

  • OR

  • LIKE

  • NOT LIKE

  • IS NULL

  • IS NOT NULL

• Functions

  • ABS

  • ADD

  • ADDPARENTCHILD

  • ARCCOS

  • ARCSIN

  • ARCTAN

  • ATAN2

  • Casting: FLOAT(), INTEGER(), DATETIME()

  • CEILING

  • COS

  • DATEADD

  • DATEDIFF

  • DATEPART

  • DATETIMEPARSE

  • DEGREES

  • EXP

  • FLOOR

  • FORMAT

  • GETDAYID

  • GETMONTHID

  • GETWEEKID

  • IFNULL

  • IIF

  • ISINF

  • ISNAN

  • LENGTH

  • LN

  • LOG

  • LOG10

  • LTRIM

  • NOW

  • NOWDATE

  • POSITION

  • POW

  • RADIANS

  • PI

  • RANDOM

  • RTRIM

  • SIGN

  • SIN

  • SPACE

  • SQRT

  • SUBSTRING

  • TAN

  • TOLOWER

  • TOTIME

  • TOUPPER

  • TRIM

• Functions which are NOT included:

  • ADDPARENTCHILD

  • DISPLAY BY

  • DISPLAY WHERE

  • DRANK

  • DTOP

  • FIND

  • FUNCTION

  • FUNTIONLOOKUP

  • GETLEVELATTRIBUTE

  • GETLEVELVALUE

  • GETPROMPTFILTER

  • GETPROMPTVALUE

  • GETVARIABLE

  • LET

  • LOOKUPROW

  • LOOKUPVALUE

  • MEDIAN

  • NEXTCHILD

  • NUMROWS

  • ORDER BY

  • PTILE

  • RANK

  • REMOVEALL

  • REMOVEAT

  • REXP

  • ROWNUMBER

  • RSUM

  • SPARSE

  • STAT

  • TOP

  • TRANSFORM

  • TREND

Expression Syntax Basics

Put column names in square brackets. For Birst experts, please note that there is no need to add aggregation syntax, just the column name will do.

[My Column Header]

Enclose strings in single quotes.

'Modeler'

Supported operators include:

=, <, >, <>, <=, >=, *, AND, OR, LIKE, NOT LIKE, IS NULL, IS NOT NULL, + (concatenate), % (modulo)

For more details, see the BQL and Expressions Reference PDF on ThinkTank.

See the other Modeler transformations at Deep Dive: Modeler Transformations Reference.