Function |
Description |
Case |
The case statement has two different forms. Simple: case Arg1 when Arg2 then Arg3 else Arg4 end The Arg1 expression is evaluated and when Arg1 is equal to Arg2 then Arg3 is returned. Multiple when/then expressions can be entered and are evaluated in left to right order. Searched: case when Arg1 then Arg2 else Arg3 end Returns Arg2 if Arg1=true, and Arg3 if Arg1=false. Multiple when/then expressions can be entered and are evaluated in left to right order. See below for more information.
Example: case when 1 < 2 then "a" when 1 < 3 then "b" else "c" end case [Column] when 3 then "a" when 2 then "b" else "c" end |
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: If(1 < 2, "Small", "Big") → Small If([Column] Is Null,"0","has value") |
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([Column] Is Not Null, "value was not null", "value was null")
If an expression contains empty values (null values), you can use the SN function to substitute the null values with the specified value. |
Is Null |
Used within an If- or Case- statement, to determine whether or not an expression yields an empty value (null value).
Example: If([Column] Is Null, "value was null", "value was not null")
If an expression contains empty values (null values), you can use the SN function to substitute the null values with the specified value. |
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 |
As you can see, the value of the percentile will change depending on how '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
See also: