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.