## **Context**
In this guide, we're going to uncover how to use hex color codes in Builder to bring qualitative crime data from Chicago to life. Ever wondered how to give each crime category its own unique color? We'll show you how to do that with randomized hex color codes. We'll also dive into setting specific colors based on conditions, tapping into the power of CARTO Workflows and SQL. Once we have our colors ready, we'll use Builder's HexColor feature to effortlessly style our layers. By the end of our journey, you'll be ready to create a vibrant and clear map showcasing the intricacies of crime in Chicago. Excited to transform your data visualization? Let's jump right in!
## **Step-by-Step Guide:**
In this guide, we'll walk you through:
* [Creating hex color codes with Workflows](#creating-hex-color-codes-with-workflows)
* [Generating hex color codes via SQL](#generating-hex-color-codes-using-sql)
* [Styling layers in Builder using hex color codes](#styling-qualitative-data-using-hex-color-codes-in-builder)
### **Creating hex color codes with Workflows**
1. Access *Workflows* from your CARTO Workspace using the Navigation menu.
2. Select the data warehouse where you have the data accessible. We'll be using the CARTO Data Warehouse, which should be available to all users.
3. Navigate the data sources panel to locate your table, and drag it onto the canvas. In this example we will be using the `chicago_crime_sample` table available in demo data. You should be able to preview the data both in tabular and map format.
4. We are going to generate random hex color codes based on distinct category values. For that, add the **Hex color generator** component into the canvas. This component will first select the distinct values of an input column and then generate a unique hex color code for each of them. In our case, we'll select `primary_type` as the column input, which defines the type of crime committed. Then, we click on "Run".
5. We can preview the data to confirm a new column named `primary_type_hex` has been added to your output table. This one contains distinct hex color values for each distinct input category.
6. Now let save our output as a table using **Save as Table** component. We will use this output later to generate our Builder map.
7. Add [annotations](https://docs.carto.com/carto-user-manual/workflows/workflow-canvas#annotations) so you can provide further context to other users accessing the Workflows.
Now that we're done with the first step of having a table ready to visualize the specific locations of crimes, we'll move to the generation of a new separate table for an extra layer in the visualization. In this case, we'll leverage [Spatial Indexes](https://academy.carto.com/building-interactive-maps/data-visualization/broken-reference) to gain insight into the ratio of arrested to non-arrested crimes. By doing so, we can better grasp the geographical distribution and patterns of resolved versus unresolved crimes.
8. First, transform the crime point location to H3. To do so, use the **H3 from Geopoint** component using `12` as the resolution level. Once run successfully you can preview the data and the map results.
9. To discern if a crime resulted in an arrest, we need to convert the `arrest` column from a `Boolean` type to a `String` type. We'll accomplish this transformation using the **CAST** component.
10. Now we can use **Simple Filter** component to identify the crimes that were *arrested* (True) vs *not arrested* (False).
11. For each **Simple Filter** output, we will add a **Create column** component where we will define a specific hex color code value to the same column named `arrest_hex` as per below screenshot. Let's also add some annotations so is clear what we are doing on these steps.
12. Now that we have generated the `arrest_hex` column, we will use **UNION** component to bring our dataset back together.
13. Finally, let's save our results in a new table using **Save as Table** component.
***
### **Generating hex color codes using SQL**
We can generate Hex color codes directly using SQL, both in the DW Console and the CARTO Platform. Within CARTO, you have the flexibility to use either the Workflows with the Custom SQL Query component or the Builder SQL Editor.
Below you can find two different examples on how you can use SQL to generate hex color codes:
* Define hex color codes using CASE WHEN statement:
```sql
WITH data_ AS (
SELECT
geom,
CAST(arrest as string) as arrest
FROM carto-demo-data.demo_tables.chicago_crime_sample)
SELECT
a.*,
CASE
WHEN arrest = 'true' THEN '#8cbcac'
WHEN arrest = 'false' THEN '#ec9c9d'
ELSE ''
END AS arrest_hex
FROM data_ a
```
* Generate random hex color code values for each DISTINCT category value:
```sql
WITH data AS (
SELECT DISTINCT primary_type
FROM carto-demo-data.demo_tables.chicago_crime_sample
),
hex_ AS (
SELECT
primary_type,
CONCAT(
'#',
LPAD(FORMAT('%02x', CAST(RAND() * 255 AS INT64)), 2, '0'), -- Red
LPAD(FORMAT('%02x', CAST(RAND() * 255 AS INT64)), 2, '0'), -- Green
LPAD(FORMAT('%02x', CAST(RAND() * 255 AS INT64)), 2, '0') -- Blue
) AS random_hex_color
FROM data)
SELECT
a.geom,
a.unique_key,
a.primary_type,
b.random_hex_color
FROM carto-demo-data.demo_tables.chicago_crime_sample a LEFT JOIN hex_ b
ON a.primary_type = b.primary_type
```
***
### **Styling qualitative data using hex color codes in Builder**
Now that we have generated our tables in Workflows containing the hex color code values, we are ready to style it in Builder using the *HexColor* functionality that allows you to style qualitative data leveraging your stored hex color code values.
14. First, let's load our first output table in *Step 6* named `chicago_crime_hex`. We will do so adding it as a SQL Query source. To do so, copy the qualified table name from the **Save as Table** component in Workflows or access the table in Data Explorer.
15. Now let's rename your map to "Crime Analysis in Chicago" and the layer to "Crimes".
16. Now open the Layer style configuration, and follow the steps below:
* In the *Color based on* selector pick `primary_type` column to associate with the hex color code.
* In the *Palette* section, click on 'HexColor' type.
* Finally, pick the column with the hex color code values, which in our instance is named `primary_type_hex`.
You should now observe the crime point locations styled based on the hex color codes from your data source. Furthermore, consult the legend to understand the association between categories and colors.
17. Change the *Stroke Color* to `black` and set the *Radius Size* to `6`.
Next, integrate the aggregated H3 grid to assess the *arrest* vs. *non-arrest* ratio. This will help pinpoint areas where crimes often go without subsequent arrests, enabling us to bolster security measures in those regions
18. For that, add a new source adding `chicago_crime_h3_hex` table created in *Step 13*. A new layer named "Layer 2" will be added to your map in the top position.
19. Rename the new layer to "Crimes H3" and move it to the second layer position, just below Crimes point layer.
20. Next step would be to style the "Crime H3" layer. Open the *Layer style* configuration. In the *Basic* section, set the *Resolution* to `3`. This will decrease the granularity of the aggregation so we are able to visualize it with the crime point location overlaying on top.
21. Now, let's style the cell using our stored hex color codes. For that, select `arrest` column in the *Color based on* section as the category using `MODE` as the aggregation method. Then, choose 'HexColor' type and select `arrest_hex` as your *Color Field*.
22. To finalise with the layer options, we will set the *Visibility by zoom level* of the "Crimes" point location layer `from 11 to 21`, so that only the H3 layer is visible at lower zoom levels.
23. Once we have the styling ready, we will proceed to add some Widgets to our map. First, we will include a [Formula Widget](https://academy.carto.com/building-interactive-maps/data-visualization/broken-reference) with the `COUNT` of "Crimes" point source.
24. To continue providing insights derived from our sources, we will add a [Pie Widget](https://academy.carto.com/building-interactive-maps/data-visualization/broken-reference) linked to "Crimes H3" source displaying the percentage of arrest vs not arrest proportion of crimes.
25. Finally, we will add a [Category Widget](https://academy.carto.com/building-interactive-maps/data-visualization/broken-reference) linked to "Crimes" point source displaying the crimes by type as below.
26. Once we have finished adding widgets, we can proceed to add a [map description](https://academy.carto.com/building-interactive-maps/data-visualization/broken-reference) using markdown syntax. In our case, we will add some explanation about how to style qualitative data using *HexColor* functionality. However, you can add your description as desired, for example to inform viewer users how to navigate on this map and obtain insights.
27. We are ready to publish and share our map. To do so, click on the [Share](https://academy.carto.com/building-interactive-maps/data-visualization/broken-reference) button located at the top right corner and set the permission to Public. Copy the URL link to seamlessly share this interactive web map app with others.
And we're ready to visualized our results! Your map should look similar to the below.
{% embed url="" %}
