Logical functions
The list below shows the logical functions that can be used in expressions.
if
or
case
for simple conditions:
case [Column1] when 10 then 'ten' else 'not ten' end
case when Column1 is not null then Column1 else Column2 end
See below for more examples.
Function | Description |
---|---|
Case
|
The case statement has two different forms.
Simple:
The
Searched:
Returns
when/then expressions can be entered and are evaluated in left to right order. See below for more information. Example:
For more examples, see below and Calculated columns based on conditions. |
If(Arg1,Arg2,Arg3)
|
Returns
Arg2 if
Arg1 =true, and
Arg3 if
Arg1 =false.
Arg1 is of type boolean, usually the result of a
comparison.
Arg2 and
Arg3 can be of any type, but must both be of the
same type or null.
The second and third argument only process subsets of the rows, which impacts all column-based and accumulating methods. See below for more information. Examples:
For more examples, see below and Calculated columns based on conditions. |
Is Not Null
|
Used within an
If - or
Case - statement, to determine whether or not an
expression yields an empty value (null value).
Example:
If an expression contains empty values (null values), you can
use the
|
Is Null
|
Used within an
Example:
If an expression contains empty values (null values), you can
use the
|
More about using if in expressions:
When you are using an If-statement in an expression, the condition in the first argument limits the values to be evaluated in the third argument. To understand how it works, first take a simple integer column as an example:
Col1 |
1 |
2 |
3 |
Insert a calculated column using the following expression:
if(Col1 < 2,
Max(Col1), Max(Col1))
This will result in a new column with two different values; one for case when the max for all values that are smaller than 2 and one showing the max value for the rest of the values (which also is the max of the entire column):
Col1 | if(Col1 < 2, Max(Col1), Max(Col1))
|
1 | 1 |
2 | 3 |
3 | 3 |
The
If
-function is designed to split the column into two
groups and then to continue evaluating the expression on the new groups.
If you are using a
Percentile
expression instead, you will get different
results on the same percentile expression depending on how the first condition
has split the column:
Col2 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
Insert two columns using the following two expressions:
if (Col2 < 4, 0,
Percentile(Col2, 70))
if (Col2 < 6, 0,
Percentile(Col2, 70))
Then the following result is obtained:
Col2 | if (Col2 < 4, 0,
Percentile(Col2, 70))
|
if (Col2 < 6,
0, Percentile(Col2, 70))
|
1 | 0 | 0 |
2 | 0 | 0 |
3 | 0 | 0 |
4 | 8.2 | 0 |
5 | 8.2 | 0 |
6 | 8.2 | 8.8 |
7 | 8.2 | 8.8 |
8 | 8.2 | 8.8 |
9 | 8.2 | 8.8 |
10 | 8.2 | 8.8 |
If
' has grouped the data.
More about using case in expressions:
When working with case expressions, the condition after
"when
" is calculated on the entire column. The argument after
"then
" is calculated on those rows defined by the
"when
"-condition. If multiple "when- then
"
pairs are included in the expression a "then
" is limited by
all previous conditions as well as the last one.
An example using a simple integer column:
Col1 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
Insert two calculated columns, called Case Min and Case Max, using the following expressions:
Case Min:
case
when [Col1]<Avg([Col1]) then Min([Col1])
when [Col1]<(Avg([Col1]) + 1) then Min([Col1])
else Min([Col1])
end
Case Max:
case
when [Col1] < Avg([Col1]) then Max([Col1])
when [Col1] < (Avg([Col1]) + 1) then Max([Col1])
else Max([Col1])
end
The result will be:
Col1 | Case Min | Case Max |
1 | 1 | 4 |
2 | 1 | 4 |
3 | 1 | 4 |
4 | 1 | 4 |
5 | 5 | 5 |
6 | 6 | 8 |
7 | 6 | 8 |
8 | 6 | 8 |