SWITCH

The SWITCH function in Infoveave evaluates a condition against an expression and returns a value if the condition is true.

Applicable to

Calculated Columns

Return Value

The SWITCH function returns the specified value if the condition is true.

Remark

The SWITCH function can be used as a more concise alternative to IF statements for simple conditional logic.

Syntax

SWITCH(<expression>, condition, value)

SWITCH in Calculated Columns

Parameter	Description
Expression	A placeholder in a function that is replaced with the column names.
Condition	The condition to evaluate against the expression.
Value	The value to return if the condition is true.

Write the SWITCH function. For instance

SWITCH(<expression>, condition, value)

1. Replace <expression> with VALUE([Column Name]), replace condition with the actual case that you want to evaluate against expression and replace value with what you expect to return when the condition turns true.Replace the Column Name with the actual name of your column required.
2. To learn how to add calculated columns in Infoveave, visit the section Calculated Columns.

Example 1

Objective Consider you want to create a pricing bucket to categorize the unit price on the below table.

ORDER DATE	COUNTRY	CATEGORY	UNIT PRICE	MARKET PRICE	QUANTITY
2024-01-03	Brazil	Baby Food	38.41	43.7874	2
2024-01-07	Japan	Spices	45.56	50.8155	20
	Japan			43.7874	10
2024-01-18				32.3019	4
2024-01-22	Brazil	Cosmetics	28.335	23.0109	7
2024-01-26	Canada		20.185	23.0109	9
2024-01-04	France	Cereal	25.26	28.7964	9
2024-01-09	Brazil	Cereal	44.575	50.8155	8
2024-01-14	Brazil	Snacks	20.185		4
	Brazil	Cosmetics	40.485	46.1529	2

You can use the SWITCH function like

SWITCH(TRUE, VALUE([UNIT PRICE]) > 40, "High Price", VALUE([UNIT PRICE]) < 40 AND VALUE([UNIT PRICE]) > 30  , "Medium Price", "Low Price")

The new calculated column “Price Bucket” will return

ORDER DATE	COUNTRY	CATEGORY	UNIT PRICE	MARKET PRICE	QUANTITY	PRICE BUCKET
03-01-2024	Brazil	Baby Food	38.41	43.7874	2	Medium Price
07-01-2024	Japan	Spices	45.56	50.8155	20	High Price
	Japan			43.7874	10	Low Price
18-01-2024				32.3019	4	Low Price
22-01-2024	Brazil	Cosmetics	28.335	23.0109	7	Low Price
26-01-2024	Canada		20.185	23.0109	9	Low Price
04-01-2024	France	Cereal	25.26	28.7964	9	Low Price
09-01-2024	Brazil	Cereal	44.575	50.8155	8	High Price
14-01-2024	Brazil	Snacks	20.185		4	Low Price
	Brazil	Cosmetics	40.485	46.1529	2	High Price

Example 2

The SWITCH function in Infoveave can also transform values based on specified conditions.

For example

SWITCH(VALUE([COUNTRY]), "Brazil", "BR", "Japan", "JP", "Canada", "CA", "FRANCE", "FR")

The SWITCH function converts the country names into their respective country codes. For example, if the original value from [COUNTRY] is “Brazil”, the SWITCH function will return “BR”. Similarly, “Japan” will be converted to “JP”, “Canada” to “CA”, and “France” to “FR”. If the value from [COUNTRY] does not match any of the specified conditions, the SWITCH function will return NULL.