Logical Functions


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([Count] > 3, "many", "few")
If(true, null, null)                        -> (Empty)
If(true, 1, null)                            -> 1
If(false, null, 2)                          -> 2
If(null, 1, 2)                                 -> (Empty)

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:

Binning functions

Conversion functions

Date and Time functions

Math functions

OVER functions

Property functions

Ranking functions

Spatial functions

Statistical functions

Text functions