IF Column
Create new columns with conditions
The IF Column tool is a very powerful way to create new custom columns. The best way to explain how it works is with an example. Think of it like choosing what to wear based on the weather. If it’s raining, you wear a raincoat. If it’s sunny, you wear a t-shirt. If it’s cold, you wear a sweater. With the IF Column tool you tell Less to make similar choices based on your data: “If this is true, do this thing. If that’s true, do that thing instead.” Just like you check the weather before deciding what to wear, Less checks certain conditions before deciding what to do next.
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 sensitive
You 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 Something
Input Column Name
The IF Column tool will create a new column. Use this input to name it.
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.
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.