Change a data type with Visual SQL steps

Still need help?

The Atlassian Community is here for you.

Ask the community

There may be scenarios where you find yourself with data not in the desired data type. For instance, you may have numeric values that are saved as string data types in your database and as a result you are unable to create a chart in Atlassian Analytics that adds up the values properly.

Visual SQL allows the use of SQLite functions and syntax in post-query manipulations to transform your data, so we can use the CAST expression to change a column's data type with Visual SQL steps.

  1. In this example, we would like to calculate the total sum of story point values. When you hover the column of the Value column, you can see that it is a “text” data type.

    When you try to apply a “Group & aggregate” step to this result set, you can see that it is not letting us apply a sum to the Value column.

  2. To change the data type of the Value column, hover over the column in the result table and select the “Apply formula” icon.

  3. Select Custom and enter the following formula using the CAST operator, converting the column’s data type to the desired data type. A CAST expression 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("Value" AS REAL)

    Changing the column to REAL enables us to use the column in calculations and get decimal precision in our results. If decimal precision is not needed, you can convert it to an INTEGER instead.

  4. Now that our Value column is casted to the “real” data type, the “Group & aggregate” step is showing other aggregation options and we can select Total sum to calculate the total sum of the numeric values for our chart.



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.

Last modified on Sep 29, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.