You may create a new dataset by querying existing datasets. This feature is particularly useful if you would like to modify your exisitng data or join data from two datasets.
- Click on Home —> Datasets.
- Click on the “+ Add New Dataset” button, and select “Query Datasets”.
- Enter the SQL query you would like to perform, and press the Run Query button. There are a few examples of queries to follow below.
Alternatively, you may use the built-in help function to generate a SQL query. Select which dataset you’d like to query, and write the question or command you are thinking of. Cast will generate a query based on your input.
In this example, we selected Sample Contacts - Copy 19 as the dataset. Our command was, “List all the contacts with renewal authority.”
Cast returned the query SELECT * FROM dataset_15067 WHERE renewal_authority = 1;
- Check the Query Execution Preview. When you are satisfied, give your new dataset a name and press the Save button.
SELECT -- Replace with the columns from your dataset id, name, CASE WHEN gender = 'Male' THEN 'M' WHEN gender IS NULL THEN 'U' WHEN gender = '' THEN 'U' ELSE 'F' END AS gender_no_empty FROM -- Replace with your dataset dataset_XXXXX as "Dataset_Name";
SELECT -- Replace with the columns to rename from your dataset "__internal.__id" as id FROM -- Replace with your dataset dataset_XXXXX as "Dataset_Name";
SELECT category, -- get the category column SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS 'Q1', -- pivot Q1 sales SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS 'Q2', -- pivot Q2 sales SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS 'Q3', -- pivot Q3 sales SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS 'Q4' -- pivot Q4 sales FROM -- Replace with your sales dataset dataset_XXXXX as "Sales_Dataset_Name" GROUP BY category;
-- Get the customer's name and the count of their support tickets SELECT "Customers_Dataset".name, COUNT("Tickets_Dataset".id) as ticket_count FROM -- Replace with your customers dataset dataset_XXXXX as "Customers_Dataset" JOIN -- Replace with your tickets dataset dataset_XXXXX as "Tickets_Dataset" ON "Customers_Dataset".id = "Tickets_Dataset".customer_id GROUP BY "Customers_Dataset".id;