Common Query Operations
The Query Builder allows you to transform your data by applying operations step by step. Each operation performs a specific task, such as selecting columns, filtering records, joining tables, creating calculations, or summarizing data. Multiple operations can be combined to create powerful queries without writing SQL.
How Operations Work
Every operation is applied in sequence. The output of one operation becomes the input for the next, allowing you to gradually refine and transform your data.
Choose Columns
The Choose Columns operation lets you select only the fields you want to include in the final result. Removing unnecessary columns makes your data easier to understand and can improve query performance.
When to Use
- You only need a subset of columns from a table.
- You want to simplify the query results.
- You want to reduce the amount of data being processed.
How It Works
Select the columns you want to keep. All other columns are excluded from the query output.
Example
From a Customer table containing twenty columns, keep only Customer Name, Email, Region, and Total Orders.
Tip
Apply this operation early whenever possible. Processing fewer columns generally results in faster query execution.
Filter Rows
The Filter Rows operation displays only the records that match specific conditions, helping you focus on relevant data.
When to Use
- Analyze data for a specific time period.
- View records from selected regions or categories.
- Exclude unwanted values such as cancelled orders.
- Remove incomplete or invalid records.
Available Filter Types
Comparison Filters
- Equals
- Does Not Equal
- Greater Than
- Less Than
- Greater Than or Equal To
- Less Than or Equal To
List Filters
- In
- Not In
Text Filters
- Contains
- Does Not Contain
- Starts With
- Ends With
Special Filters
- Is Set
- Is Not Set
- Between
- Within (relative date ranges such as Last 7 Days or Last Month)
Combining Filters
- AND – All conditions must be true.
- OR – Any condition can be true.
Examples
- Show orders from the last 30 days.
- Display only North and South regions.
- Exclude cancelled orders.
- Show customers with email addresses.
Tip
Apply filters early in your query to reduce the amount of data processed by later operations.
Join Table
The Join Table operation combines data from two related tables using matching values such as IDs.
When to Use
- You need data from multiple tables.
- You want to enrich records with additional information.
- You need to combine related datasets.
Available Join Types
Inner Join
Returns only rows that exist in both tables.
Left Join
Returns all rows from the current table together with matching rows from the joined table.
Right Join
Returns all rows from the joined table together with matching rows from the current table.
Full Join
Returns all rows from both tables, regardless of whether matching records exist.
How It Works
- Select the table to join.
- Choose the join type.
- Select the matching columns.
- Choose which columns to include from the joined table.
Example
Join the Orders table with the Customers table using Customer ID to display customer information alongside each order.
Tip
Left Join is the most commonly used join because it preserves all records from your primary table.
Append Table
The Append Table operation combines rows from another table by placing them underneath the current dataset.
When to Use
- Merge multiple time periods.
- Combine data from different regions.
- Analyze similar datasets together.
Options
- Include Duplicates – Keeps every row.
- Remove Duplicates – Removes duplicate records.
Example
Combine separate Q1 and Q2 sales tables to analyze sales for the first half of the year.
Note
Both tables must contain at least one matching column name. Only common columns appear in the final result.
Add New Column
The Add New Column operation creates calculated fields using formulas based on existing columns.
When to Use
- Create derived metrics.
- Perform mathematical calculations.
- Combine text fields.
- Implement business calculations.
Common Examples
- Profit = Revenue − Cost
- Profit Margin = (Profit ÷ Revenue) × 100
- Order Total = Quantity × Unit Price
- Full Name = First Name + Last Name
How It Works
- Provide a name for the new column.
- Select its data type.
- Write the calculation using existing columns.
Tip
Use descriptive names for calculated columns so they are easy to identify in charts and dashboards.
Group & Summarize
The Group & Summarize operation groups records into categories and calculates summary statistics such as totals, averages, and counts.
When to Use
- Calculate totals by category.
- Generate summary reports.
- Analyze trends over time.
- Create aggregated metrics for charts.
Available Summary Functions
- Sum – Total value.
- Count – Number of records.
- Count Distinct – Number of unique values.
- Average – Mean value.
- Minimum – Smallest value.
- Maximum – Largest value.
Time-Based Grouping
When grouping date fields, you can summarize data by:
- Second
- Minute
- Hour
- Day
- Week
- Month
- Quarter
- Year
Examples
- Total Sales by Region.
- Average Order Value by Product.
- Monthly Revenue.
- Customer Count by City.
Tip
You can group by multiple dimensions and calculate multiple summary measures within a single operation.
Custom Operation
The Custom Operation allows advanced users to perform transformations using Python expressions.
When to Use
- Standard operations cannot achieve the required transformation.
- You need advanced calculations or business logic.
- You are comfortable writing Python expressions.
Capabilities
- Access existing columns as variables.
- Perform complex calculations.
- Apply advanced conditional logic.
- Create custom data transformations.
Important
Custom Operations require knowledge of Python. Use them only when the built-in Query Builder operations cannot achieve the desired result.
