Query Builder
Introduction to Query Builder
The Sheetloom Query Builder is a tool that allows users to create a simple SQL statement with no or minimal knowledge of SQL.
The builder works with a database table created from a source CSV or PDF file, with an associated Athena database connection which was automatically created when the original data source file was loaded. When saved, the query is added to the Stitch query panel.
Building a Query
Navigate to the Patterns page and click Create Stitch. In the dialogue box select the Pattern Namefrom the dropdown to save the Stitch to, and give the Stitch a meaningful name.
Choose Type = SQL and Connection = athena. When Connection is set to = athena the Query Builder button displays.
Click Query Builder to launch.
The Query Builder Panel
Query Builder Elements
The SQL for the query is displayed along the bottom of the Query Builder. This updates automatically as the query develops. It can be tested at any time by clicking on the Test Query button.
CSV Tables
From the dropdown list [1] select the source CSV table being used in the query.
Columns
By default the first column from the CSV is displayed [2]. If this is not required for the query it can be removed by clicking X at the end of the row [3].
To add a new column, click the green + in the Columns area [4]. A new row displays. Click the dropdown and select the required column.
Repeat to add columns.
Functions
There are several functions available for aggregating data or converting dates. These are accessed by clicking on the Functions dropdown [5]. If no aggregation is required the function can be left on its “none” default setting.
Arithmetic Aggregations
Select the required aggregation from sum, average, min, max or count. Date Parse is explained in the following section.
Date Parse
The database table holds date entries as a text string that need to be converted to a date format using the built-in date parse feature.
Refer to the source file to determine how the date is formatted (e.g. it may appear as “YYYY-MM-DD” / “2024-11-23” , “DD-MM-YY” / “23-11-24”, “MMDDYY” / “112324”, and so on).
Add the date field column to the query and select date parse from the Functions dropdown [5]. The dropdown on the Date Format column [6] becomes active.
Select the date format from the dropdown[6] that corresponds with the source data format.
Notice that the SQL query at the foot of the Query Builder updates dynamically to display the date parse command.
If a column uses an aggregation, SQL rules require that all other columns must also have an aggregation applied, or be included in the Group By clause.
Delete A Column
Click the red X at the end of the row to remove a column from the query [3].
Query Filters
Optional filters restrict which rows or groups are included in the results based on specified criteria.
Add a Filter
In the filter dropdown [7], select the column to filter on. A condition and a value box display.
The Filter condition box shows options depending on the data type. For example, a numeric column will show arithmetic operations such as equal to or greater than; a text column shows options such as equal to, contains, begins with.
Select the condition to apply to the filter, and the filter value.
In the example shown the query will return transport modes that equal Sea. If the NOT button [9] is checked the query will return results that do not match the specified condition: in the example it will return transport mode rows that do not equal Sea.
To add further filter columns, click add rule [8] and follow the process described.
The Query builder permits filtering on individual dates, but not on a date range. The SQL for this part of the query must be provided in the Stitch configuration query panel.
Filter Grouping
When AND / OR operators are combined in a query, the AND operator is evaluated first by default. To control the order of evaluation, parentheses is used to group filters. Conditions within parentheses are executed first.
This is done by adding a group to a filter.
With the top level filter in place, click Add Group[10]. Select the first column to be used in the OR or AND statement, and the condition and value. Click Add Rule on the row of the group filter [11], select the second column for the statement, and add its condition and value.
In the example shown, the query returns all jobs for the Mexico office where the job profit was greater or equal to 3000 or less than or equal to 500 (perhaps used to see which are the good and bad jobs). The generated SQL statement shows the full query
The conditions in the parenthesis are evaluated first, preventing any logical errors.
Test Query
To verify that the query is working as intended click Test Queryon the bottom of the builder panel to generate a preview. The first ten results are shown.
Click on Save Query. The query is made available in the create Stitch panel where the Stitch configuration can be completed.
