SWITCH function

  • 21 December 2021
  • 3 replies
  • 1837 views

Userlevel 2
Badge +3

Description

Compares one expression or block against a list of conditions and results, and returns the result of the first matching condition. If there is no match, returns an optional default result or blank if there is no default.

 

Syntax

SWITCH(Expression, Value1, Result1 [, ValueN, ResultN] [, Default])
 

Argument

Type Dimensions Description
Expression Any Any Can be a List Property, a Metric, a List or a Formula returning a result. Each value of the Expression will be compared to the values provided in the Switch.
Value1 Any No Dimension

At least one pair of {value, result} is required.

Must be a scalar value and of the same data type as Expression. A scalar in this context is a singular (not dimensioned) value.

Result1 Any Any The result when a value of Expression matches Value1.

ValueN

(optional)

Same as Value1 No Dimension Same as Value1: you can provide any number of pairs, so you can have Value2, Result2, Value3, Result3...

ResultN

(required for each ValueN)

Same as Result1 Any Same as Result1.

Default

(optional)

Same as Result1 Subset of Result1 Optional argument that defines the output when there is no match for any Value, including blank values from Expression.

 

Return type

Same data type as the Result values.

 

Example

Note that to improve readability, especially with multiple {value, result} pairs, it is recommended to put each one on a separate line.

Case

Return Type

Result
SWITCH('Status', 0, "Inactive", 1, "Active") Text
BR has blank and non-matching values, so the results are blank (no default value)
(click to enlarge)

 

SWITCH('Status', 0, "Inactive", 1, "Active", "Decommissioned")

Text

BR has blank and non-matching values, so the result has the default value
(click to enlarge)

 

SWITCH('Code', "A", 1, "B", 2, 'Code Default') Number
Similar example with a Metric used as Default
(click to enlarge)

 

SWITCH(Region, Region."EU", "Europe", Region."NA", "North America", Region."LATAM", "Latin America") Text
Switch on a Dimension
(click to enlarge)

 

 

Tips

When using the Switch function with Dimension items, a common mistake is to specify values as strings instead of “references”. The main risk with strings is that it’s not future proof, e.g. renaming the value in the source Dimension could silently break some Switch cases:

❌ Not recommended ✅ Do this instead
click to enlarge
If you rename “NA” to “North America” in the Region dimension, the Switch will still have “NA” and won’t match that value anymore.
click to enlarge
Notice how Region.Name became just Region. Now if we rename any value in the Region dimension, it will update the value in the Switch automatically as it’s referencing the dimension item, not the literal string.

 

Note that this is true not only for the Switch, and is a best practice to follow as much as possible.

 

Excel equivalent: SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])

See also: IF

More of a hands-on learner? 

Talk to your Customer Success Manager about downloading the Functions and Modifiers in Pigment Application into your workspace.  It includes examples of every formula and modifier in Pigment!

 

​​​​


This topic has been closed for comments

3 replies

Userlevel 2
Badge +9

The following give me an error : SWITCH('Finished Goods'.Code, "111", '111-Main-Floral-Wall', "112", '112-Main-Floral-Not Wall', 0).

Any idea why ? I only get a “formula error” message.

 

Notes :

  • It works with imbricated IF formulas (but … 🙄)
  • 111-Main-Floral-Wall and 112-Main-Floral-Not Wall are metric numbers.

 

Thanks in advance !

Userlevel 6
Badge +14

Hi Camille,

Not sure about this specific context but I reckon you have to be explicit when using the SWITCH function. What can be done is pasting most parts of your function (e.g. Entities.Allocation Rule, ‘Allocation - A’, ‘Allocation - B’) and make sure they all have the same structure (same dimensions returned).

Making sure the target format was right was also a good idea!

Userlevel 2
Badge +9

Hi François,

It seems that the metrics are causing the error If i change from 

SWITCH('Finished Goods'.Code, 111, '111-Main-Floral-Wall', 112, '112-Main-Floral-Not Wall', 0)

to

SWITCH('Finished Goods'.Code, 111, 0.4, 112, 0.6, 0)

It works fine.