Data values in Sigma each have a type. Some data types are numbers, dates, and text. Each value has a specific type associated with it, and that type governs rules about how it will be displayed and what kind of operations are allowed on it.
Most of the time, types are something that operates in the background of Sigma and you don’t need to worry about them. However, it can be useful to understand types and how to change data types when writing some formulas.
Numbers: 0, 1, 2.1
Text: “St. Louise”
Logical Values: True and False
Note that values in a Sigma Worksheet are derived from a linked Database which has its own system of types. Database types are intended to optimize the storage of data and are much more complex. Sigma simplifies that by consolidating database types into this short list.
However, if a Database table contains a type that doesn’t correspond to one of Sigma’s own, Sigma treats those values as text. In the future Sigma will support more types of Values as needed.
Dates in Sigma can represent either a specific calendar date, or a specific date and time.
Null represents the absence of value. In the worksheet Null is displayed as an empty cell. Null values can appear anywhere for a number of reasons:
- Null values were already present in the linked database table
- A formula could not compute a value, like 100 / 0
- A corresponding row could not be found in a joined table
In many cases passing a Null to a function which is not expecting it will cause a Null result. This can lead to some odd results:
3 = 3 // True
3 = 1 // False
3 = Null // Null
Null = Null // Null
This behavior helps Sigma to continue in spite of what would otherwise be an error, at the expense of sometimes obscuring the source of the error. In cases where a possible Null value is expected, functions like IsNull and Coalesce can be helpful.
Null values may be filtered away, just like other types of Values.
Functions and Types
Each function and operator has rules about the types of arguments it allows. If an argument is not of an allowed type, the formula editor will display a warning and the column will be in an error state.
Function documentation will indicate if there are restrictions to argument types that can be used with that function.
Sometimes a value will not be recognized as the correct type, or a formula requires you to specify the type of entry. Use an appropriate type-conversion function to ensure the data is treated as the correct Type.
Most data warehouses support some form of composite data within columns. (e.g. nested arrays, nested structures, JSON or XML) Sigma refers to these as variants. Sigma gives you an interactive visual interface to extract columns of data from variant columns. Simply open the column menu and choose Extract Columns.