The Visual SQL Pipeline allows you to use SQLite functions and syntax to manipulate your data. Please refer to the list of core functions that can be used in the Pipeline to learn more about the different options available and how you can use them to transform your data in Chartio.
One of these functions can be used to change a column’s data type. When we use a Custom formula in a Calculated Column or Edit Formula Action , we can use the
cast function to change a column’s data type.
cast statement follows the format below, where you specify your column name and the use the word
as followed by the data type the column should be converted to.
cast("your_column_name" as type)
In the example below, we change the “Count of distinct Tickets Id” column’s data type from an integer to real. Changing our numeric column to real enables us to use the column in calculations and get decimal precision in our results. Therefore, we used the following formula in our Edit Formula Action to change our column to real:
cast("Count of distinct Tickets Id" as real)
Another method you can use to convert an integer to a real is to divide the column by
1.0. Since we’re dividing an integer column by a real, the new values will be of real data type. Using a value of
1.0 also preserves the original values of the result set. In the example below, we use an Edit Formula Action and select the Divide Guided Formula.
You can read more about the different data types in SQLite’s documentation. Check out how to change a column’s data type in your data source’s schema if you want to avoid doing this in every chart.