0

I have a dataset in Carto Builder with a column that lists percentage values with a percent symbol (e.g. "10%", "50%", "35%"). By default Carto has categorized the fields in this column as strings. I want to use this column to apply shading to a choropleth map based on percent. Is there any way to get Builder to process this column numerically?

If I try to convert the data type of the column from String to Number all the fields become null. I can do the conversion in my source program but I am looking for a way to do this all in Carto Builder.

2 Answers 2

1

You can achieve this using simple SQL:

SELECT 
  *, 
  substring(string_field FROM '[0-9]+')::numeric as num_field 
FROM
  table_name 

Remember to apply this query to the node data source. If you have some analysis attached, you would need to drag that node out. More about substring Postgres function here, and BUILDER analysis workflow here.

1

You need to remove % char. I'd try these two options, replace table and field names with yours:

a) before changing field type remove them with update <table> set <field> = rtrim(<field>,'%')

b) use custom SQL to get data, something like select *,to_number(<field> ,'999%') from <table>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.