Nesting Conditional & Logical Operators

Overview

In this article, we explain how to write and use nested conditional and logical operators in ETL Queries.

Logical Operators

Here, will go over logical operators and how to run nested if statements using ‘M-code’.

There are three main types of logical operators:

• AND
• OR
• NOT

Consider the following Data:

Adding Custom Columns

With the above available operators, you could create three simple custom columns, that apply the AND, OR, and NOT operators in ‘M’-code, as illustrated here:

Using an AND operator:

Using an OR operator:

And finally, using a NOT operator:

The resulting query will look as follows, with the three new columns, obeying the above logic:

Filtering Columns

Logical Operators can also be used when filtering columns. For example, when you try to filter the ‘Data’ column above, you will be presented with the following. Here, you have the option to add several clauses, switching between AND and/or OR operators, for each one. This allows you to build quite sophisticated logic into your queries.

Nested Statements

Logical operators can also be nested inside IF / ELSE statements, that use the following pattern:

If (logical test) then (result if true) else (result if false)

Take the following example:

The interest due on each account can be calculated using the following logic:

The result is the following, with the ‘Interest’ column added at the very end: