The IF Column tool is also known as conditional statements, and is similar to a CASE statement in SQL and an =IF() function in Excel
Our example visualised. Note that each row in your columns will go through this decision tree.
Our example in Less
Syntax
| Syntax | Meaning | Example | Condition |
|---|---|---|---|
== | Equals | [NumberColumn] == 1 | All rows where NumberColumn is equal to 1 |
!= | Does not equal | [TextColumn] != "Sales" | All rows where TextColumn does not equals Sales |
< | Less than | [NumberColumn] < 20 | All rows where NumberColumn is less than 20 |
> | Greater than | [NumberColumn] > 100 | All rows where NumberColumn is larger than 100 |
<= | Less than or equal to | [NumberColumn] <= 0.5 | All rows where NumberColumn is less than or equal to 0.5 |
>= | Larger than or equal to | [NumberColumn] >= 150 | All rows where NumberColumn is larger than or equal to 150 |
.contains() | A text column contains a certain value | [TextColumn].contains("Hello") | All rows where TextColumn contains “Hello” - for instance “Hello world” |
.startswith() | A text column that begins with a certain value | [TextColumn].startswith("Hello") | All rows where TextColumn starts with “Hello” - for instance “Hello world” |
.endswith() | A text column that ends with a certain value | [TextColumn].endswith("world") | All rows where TextColumn ends with “world” - for instance “Hello world” |
.isna() | Null | [NumberColumn].isna() | All rows where NumberColumn is null |
.isnull() | Null | [TextColumn].isnull() | All rows where TextColumn is null |
& | AND -> Used to pair multiple statements together | ([NumberColumn]==1) & ([TextColumn=="Client") | All rows where TextColumn is equal to “Client” AND NumberColumn is equal to 1. Note that both conditions must be fulfilled for the statement to be true |
| | OR -> Used to determine if any conditions in a test is true | ([NumberColumn]==1) & (([TextColumn=="Client") | ([TextColumn]=="Customer")) | All rows where TextColumn is equal to “Client” OR “Customer” AND NumberColumn is equal to 1. Note that just one of the OR conditions must be fulfilled for the statement to be true |
The
.contains(), .startswith() and .endswith() is case sensitiveYou must use parentheses around the condition when you use either AND or OR clauses.This does not work:
[TextColumn=="Client" & [TextColumn]=="Customer"But this does work: ([TextColumn=="Client") | ([TextColumn]=="Customer")You can pass as many AND and OR clauses into a statement as you want as long as you remember parentheses.Configuration
The Difference tool consists of two required input.Your conditions are evaluated in the order you create them. In other words, if a cell value is equal to
Hello World and your first condition is IF .startwith("Hello") THEN "Something" and your second condition is IF .endswith("World") THEN "SomethingElse" then your output will rely on the first true condition which will lead to an output of Something1
Input Column Name
The IF Column tool will create a new column. Use this input to name it.
2
Add Your Condition(s)
Add your conditions and then clauses. The first set of condition and clause is required but additional ELSEIF pairs are optional.
3
Add Else Clause
Add a fallback value (else clause). All the rows that doesn’t fulfill any of your conditions from Step #2 will get this value.
When To Use
You can use the IF Column tool for an almost endless amount of a use cases. Every time you need to create custom columns based on different scenarios you should use the IF Column tool. Check out the examples for a couple of ideas.Examples
Custom Shipping Fee
Custom Shipping Fee
I want to calculate a custom shipping fee based on order total, order destination/location and shipping method
Hospital Patient Triage
Hospital Patient Triage
I want to create a logic for evaluating hospital patients
Expense Category Flagging
Expense Category Flagging
I want to create a custom logic for flagging expenses for review