Question
I'm using a spreadsheet or similar at source for a Quantive insight. My insight outputs a text value instead of a number. How do I make it output a number instead?
Answer
You must cast your value to the desired data type in your insight SQL. For example:
SELECT week::int FROM jira_gc_irt WHERE id::int=4
or
SELECT week::numeric FROM jira_gc_irt WHERE id::int=4
In the above examples, the Google spreadsheet I've connected was treating the week field as text, so the values for week come through in Quantive as text. Using the cast operator :: you are specifying that the value of your week field should be output as a number. This is possible because the values in the week column are actually 1,2,3,4... If they were Monday, Tuesday... it would have been impossible to convert that to a number.
Additional information
In this article we use the :: operator to cast to a numeric type. You can read more about it here: PostgreSQL numeric data types