TypeCast
The TypeCast() function takes a data range as input and casts the columns or rows of that range according to a specified list of data types.
Related functions: Transform
Syntax:
Qx.TypeCast(Range, Data Types, [Mode], [Cast Error Value])- In Excel with Quintessence add-in versions prior to 25.1, use:
TypeCast() - In the Quintessence Editor (Studio) using Quintessence language, use:
TypeCast() - In the Quintessence Editor (Studio) using Python language, use:
Qx.TypeCast()
ⓘ See parameter descriptions
| Range | The range of data used as input. |
| Data Types | A comma separated list of data types to apply to the rows or columns. One data type must be supplied for each row or column being cast. Valid data type values: • text or t• number or n• date or d• boolean or b |
| Mode (optional) | Specifies whether casting applies to columns or rows. |
| Cast Error Value (optional) | The value to return when a cast fails. |
Remarks:
- The default parameters are:
- Mode:
Column - Cast Error Value: Value is left blank (empty)
- Mode:
Examples:
The examples provided below are based on fictitious data for illustrative purposes only.
> Example 1: Typecasting columns with specific error text
Range (A1:D3):
| AAA | 10 | 2 | 1 |
| 1/30/1900 | 20 | A1 | 2 |
| 3 | BBB | 4 | 1 |
Objective:
- Convert column 1 → number
- Convert column 2 → date
- Convert column 3 → text
- Convert column 4 → boolean
- If any conversion fails, insert conversion failed
Function call:
=Qx.TypeCast(A1:D3, "n, d, t, b", , "conversion failed")Result:
| conversion failed | 1900/01/10 | 2 | TRUE |
| 30 | 1900/01/20 | A1 | conversion failed |
| 3 | conversion failed | 4 | TRUE |
> Example 2: Typecasting rows with default error text
Range (A1:C2):
| CCC | 10 | 2/18/1982 |
| 1 | 0 | 10 |
Objective:
- Convert row 1 → number
- Convert row 2 → boolean
- If any conversion fails, leave the value blank
Function call:
=Qx.TypeCast(A1:C2, "number, boolean", "row")Result:
| 10 | 30000 | |
| TRUE | FALSE |