Types of Query Columns
When creating a new column within the query builder, you are given a number of options.
Column Freeform
This is the most common column type. When you choose to include all the fields within a source, this is the type of columns that are created.
With a Column Freeform, you first need to set the name and data type.
After that you can set the Aggregate Function. This is used to combine the value of all cells in the column to one value. Different functions require a specific data type in order to work.
- Sum, Average: Numerical data types only
- Max, Min: Bit data types (ex: STRING, FLOAT, DATE)
- Count, Count_Distinct: All data types
Format Date
Setting the sort allows the data within a query to be organized in either ascending or descending order.
Date
Date Format
This column type allows you to pull a date value from any input and transform the format to whatever you see fit.
Pre-Built Dates
This type uses some pre-built types to create a column based on an existing date column from the source. The pre-built values can be set to be the same day, or the previous day, as the original date. The column can be set to weekly start/end date, which allows values to be sorted by week instead of day. Also the value can be set to "Days between Dates", which will show the difference between two separate date fields.
Date Range Labels
This column type can be created manually, or directly from the "Date Range" tab within the query. A Date Range must be created in order to use this column, and it will display what date range that each row of data fits into. This is useful if you have more than one Date Ranges in action.
Fiscal Calendar
This column type requires that a Fiscal Calendar Source be created beforehand. Select fields from the Calendar are used to query the data within a set column.
Transform
Lookup
This column type requires that a Lookup Source be created beforehand. After selecting a Lookup, you can choose a column that you would like to query (NOTE: the column must use the data type STRING). In addition, a Lookup Expression can be included.
Join
This column type allows you to join data from any source or query with the query you have created. Once an input is selected, you must select how you want the data to be joined. Then select which fields you wish to include in the query. Finally you can set up specific conditions that limit what data you want to join.
Concatenate
This column type will combine the values of two or more fields, regardless of data type, into one value. The delimiter is used to determine what you want to use to separate each value.
Calculated Metrics
This column type is used to create equations using your fields to get desired values. A field must be a FLOAT or INTEGER data type in order to be used in a calculated expression. There is also the option to allow safe division by zero in case there are instances where some of the values within a field are zero.
Case/When
This column type is one of the most versatile types available. It allows for the creation of case statements that compare a selected field with either a static value or another field. You can also set Then/Else statements to output a desired value based on the outcome of the condition(s). Unlike with Calculated Metrics, this column type is not restricted by a field's data type. Allowing you to make conditional statements using STRING and DATE values.
Split
This column type allows STRING fields to be separated into multiple columns. Once you choose a column to split, the 'Split On' box must be filled with the character you want to split with. Then you must set the prefix for the columns that will be created. Finally set the 'Split Capture Count' to determine how many times you would like to split the value. Each column created will use the Prefix followed by an incrementing number as its name.
Meta Date
This column type allows you to add columns based on the metadata of the inputs. This includes the input's name, size of the file, time of the last successful load, number of rows, etc.
SQL Expression
This column type gives you the ability to directly create SQL expressions within the query. While it does give the most freedom and options, it is by far the most complicated column type and should only be used if you are familiar with writing in SQL.
Flatten Column
This column type only works with inputs that have a RECORD data type. This will separate all of the values inside the record into multiple columns, with the Column name set as its prefix. Note that there can only be one Flatten column in a query.