Combine control Cells with SQL

Reference control cells in your query to link the results and all downstream cells to the control cell.

1. Add in one/multiple Date picker control cell/s

2. Reference your control cell in your SQL query

select * from table where date_field between (select value from start_date_control_cell) and (select value from end_date_control_cell)

3. Now any changes to the control cell will update your query and all downstream elements including visuals, cells and tables.

Create custom date aggregations to use as filters.

1. Add a Multiple select control cell

2. Click on the cell then navigate to Design panel -> Multiple select -> Static

3. Define the input Type (string for custom dates) -> Add in your filter Options one-by-one

Below we have added in options for Daily, Weekly, Monthly and Yearly. Once added, these will then appear as options in your control cell.

4. Use a case when to add the control cell options into your query, the below code below can provide an outline for your code.

select

case when value = 'Daily' then day

when value = 'Weekly' then date_trunc(day, week)

when value = 'Monthly' then date_trunc(day,month)

else date_trunc(day, year) end as date,

artist,

sum(streams)

from `count-data`.spotify.spotify_daily_tracks, custom_date_select

group by 1,2

Tip: Ensure an option has been selected in the multiple select, or the query has no reference point.

You can combine multiple control cells and reference all of these conditions in your query which will then impact all elements downstream. Let's expand on our previous example.

Now we want to be able to adjust the start and end date of our dataset, and also select one or more artists.

• Add in a Start Date and End Date date picker control cell
• Add in a multiple select control cell, setting the cell/table datasource and column as the current datasource and required filter.
• Use a where statement in the query combined with select value from control_cell to reference your new control cells.

Control cells can be used in a workflow to create a key-word based search app.

Step 1: Create search input

• Add a String input control cell, this will be your search bar.
• Reference the control cell in your query, using regexp_contains() to add in advanced search conditions (e.g. lower/upper case).

Step 2: Use control cells to reference the results

• Use the results of step 1 by adding in another control cell e.g. a Single select, and reference the 'results' query as the cell/table and column for your control cell.
• We can keep adding in control cells, for example below, we have added in a Number input control cell to apply an interactive 'rank' filter to our dataset.

The result is a query which has taken a manual search term and output a filtered result using interactive controls.