Skip to main content

Calgary Input Tool Icon Calgary Input Tool

One Tool Example

Calgary Input has a One Tool Example. Visit Sample Workflows to learn how to access this and many other examples directly in Alteryx Designer.

Use Calgary Input to query a Calgary database.

Important

As of the Alteryx 5.0 release, Calgary indexes are case insensitive. Calgary databases created in a version earlier than 5.0 will still be case-sensitive.

Configure the Tool

Calgary Database File: Specify the Calgary database to select records from, either key in the file path location of the input or select Browse to navigate to the file location.

Linked Data File: If the Calgary Database specified above (Primary file, Household) has been linked to another Calgary Database file (Secondary, Individual), the linked file will be exposed here with a few different options for returning records and/or attributes from the linked file.

Linked Options: The different linked options will return records that meet the query criteria built in the subsequent section. It is important to understand that the records returned consist of a combination of the Primary file and Secondary file only when the query criteria is met.

All Matches: Primary use of this option is when an OR Query is specified. Returns ALL Primary file records and ALL the Secondary file records that meet the query criteria.

The fields from either table can be all NULLs if the record did not link.

All Linked Matches: Returns only the Primary file records and Secondary file records that are LINKED AND meet the query criteria.

The secondary tables fields can all be NULLs if there was no matching linked record.

First Linked Match: All of the Primary file records that meet the query criteria are returned as well as the first linked record from the Secondary file that also met the query criteria.

Last Linked Match: All of the Primary file records that meet the query criteria are returned as well as the last linked record from the Secondary file that also met the query criteria.

Linked Count Only: Only a count is returned representing how many of the Secondary file records met the specified query criteria.

The primary tables fields are still output as normal.

None: No Secondary file records are returned, but the fields from the Secondary file are still available to specify in the query criteria.

Fields: The Fields area displays every indexed field available to query from. Data is organized based on the field heading structure.

Indexed fields with the names Person_First_Name and Person_Last_Name are logically grouped under the heading Person in the fields selection tree.

To select a field by which to query the Calgary database, either double-click on the field to access the Edit Query Item or highlight the field and select the Field icon.

The Field icon can have 2 different displays:

  1. If the Field icon does not have a downward arrow, there are no predefined queries set through the Edit Index MetaInfo interface.

  2. If the Field icon contains a downward arrow, you can opt to select Custom, which will provide you with the Edit Query interface to create a selection, or to select a predefined query that was previously defined through the Edit Index MetaInfo interface.

Users can edit and define the metadata used to support the field indexes by right-clicking in the Fields area and selecting Edit Index MetaInfo.

Through the Meta Info Editor, users can...

Organize and rename indexed fields to properly have them display in the Fields window.

To organize the content in the Fields window, select and drag a field to the appropriate location in the field tree. In order to rename a field, right-click the field and specify a new name.

  • Change the Index Properties of a field.

General tab: The General tab enables users to set a default query type, short field name, and long field name where the short field name is the display name for the field in the Fields window.

Range tab: The Range tab enables users to predefine a range query or queries to be available through the Field dropdown. Users can also rename any value and assign a value description through the Properties section to further define each individual value.

Values tab: The Values tab enables users to predefine an Is or Is in List query or queries through the Field dropdown. Users can rename any value and assign a value description through the Properties section to further define each individual value.

Additionally, a Tree of Values can be imported to make selection easier. To import a value tree...

  1. From the Organize Fields, select the field to create the tree for.

  2. From the Values Tab, select A Tree of Values.

  3. Specify the file to use for the Tree.

Edit Query Item: The Edit Query Item interface is a query builder that enables users to create a selection from the Calgary database based on their filter parameters. Users can select the Inverse of any query set up through the Edit Query Item interface by checking the box labeled Inverse (Not). The types of queries that can be performed are...

  1. Is...: An Is query allows you to filter an indexed field by one value.

    • "Person_First_Name Is Jim" returns all records where a person's first name is Jim.

  2. Begins With...: A Begins with query allows you to search an indexed field by a value that appears at the beginning of the field.

  3. Is in Range: An Is in Range query allows you to filter an indexed field where records fall within a range of values. You can choose to include either the beginning or end range value in the query results.

    • "Person_Average_Age Is Between 20 and 35" returns all records where the average age falls within the selected range.

  4. Is in List: An Is in List query allows you to search an indexed field for multiple values.

    • "Person_First_Name Is Jim, John, or Jack" returns all records where a person's first name is Jim, John, or Jack.

  5. Is Null: An Is Null query allows you to select all records where an indexed field contains null values.

    • "Person_First_Name Is Null" returns all records where the person first name field is null or blank.

Query: The Query area will display the filters created through the Edit Query Item interface and provide you with an area to organize and group filters to effectively retrieve data from the Calgary database.

  • Insert: The Insert dropdown enables users to set up query groupings by inserting And, Not:And, Or, Not:Or, or All parameters.

  • Edit: The Edit option enables users to modify a filter present in the Query area.

  • Delete: The Delete option enables users to remove a filter from the Query area.

  • Up/Down: The Up/Down options enable users to reorder the query filters in the Query area.

Output Count Only: Selecting this check box will return the count of all records matching the Calgary query criteria. By default, this box is not checked and will return all records matching the Calgary query criteria.

Skip Records: The ability to remove records returned by the Calgary query.

Max Records: The ability to limit the records returned by the Calgary query. This can be useful for testing purposes and trial runs.

Switch to Manual Editor: The ability to construct a query on the Calgary database through an editor. All query syntax is written in XML.

In this section: