Calgary Join Tool

The Calgary join tool provides users with the ability to take an input file and perform joins against a Calgary database. The tool can generate 6 different result types:

1. Return a count of all records that match any Input record
2. Return all records that match any Input record
3. Return a count of all records that match all Input Records
4. Return all records that match all Input records
5. Append a query count to each Input record
6. Join all results to each Input record

Configure the tool

Calgary Database File: Specify the Calgary database to select records from, either type in the file path location of the input or click 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 either returning records or attributes from that 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 will 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.

Action: Select the result type generated by the Calgary Join tool. Options include:

• Count Query Results that Match ANY Input Record: A single field, "Count," is returned and populated with a count value. The count value is indicative of how many Calgary records matched the specified query relative to any of the records coming into the tool.
• Get Query Results that Match ANY Input Record: Calgary records are returned that matched the specified query relative to any input record.
• Count Query Results that Match ALL Input Records: A single field, "Count," is returned and populated with a count value.  The count value is indicative of how many Calgary records matched the specified query relative to all of the records coming into the tool. This means all of the records coming into the tool should have a commonality that matches the query criteria, otherwise a count of 0 will be returned.
• Get Query Results that Match ALL Input Records: Calgary records are returned that matched the specified query relative to all input records.
• Append Query Count to Each Input Record: Input records are returned with an additional Count field. The Count value is indicative of how many Calgary records matched the specified query relative to each input record.
• Join Query Results to Each Input Record: Calgary Records are returned with each input record it matched appended.

If you select Join all results to each Input records, you will have the option to Include Unmatched Input records or not.

Query Criteria tab

Input Fields: Select the fields from the Input file to join to the Calgary database.

Field Properties:

• Index Field: Select the field from the Calgary database to join to the Input file.
• Query Type: Select how to join the Input file to the Calgary Database. Query Types Include:
• Exact Value -  Will join the Input file and the Calgary database where the input field is identical to the index field
• String - Begins - Joins the Input file and the Calgary database where the beginning of the input field matches the beginning of the index field.
• String - Ends - Joins the Input file and the Calgary database where the ending of the input field matches the ending of the index field.
• String - Contains - Joins the Input file and the Calgary database where the input field is contained within the index field.
• Range - >= Begins AND <End - Joins the Input file and the Calgary database where the input field falls within a defined range. The defined range does not include the end range value.
• Range - >=Begins AND <=End - Joins the Input file and the Calgary database where the input field falls within a defined range. The defined range does include the end range value.

For the Range query types, you must specify the End of Range value.

Additional Query Criteria tab

Users can filter the Calgary database. See Calgary Input Tool.

View the output

All fields going into the Calgary Join as well as all fields in the Calgary file will come out of the tool. To limit the amount of fields, use a Select Tool after the Calgary Join.