Skip to main content

Calgary Cross Count Append Tool Icon Calgary Cross Count Append Tool

One Tool Example

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

Use Calgary Cross Count Append to take an input file and append counts to records that join to a Calgary database. The tool can generate 2 types of results:

  1. Create single CrossCount results that match any input record.

  2. Create CrossCount for each input record.

Importante

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 either returning records or attributes from that linked file.

  • Linked Options: The different linked options 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.

Select the result type to be generated.

Join 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: Joins 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.

Cross Count Fields Tab

From the dropdown menu, users can choose which data fields to aggregate in order to attain a record count per group. Users can define multiple Calgary data fields to group by as well as create custom data fields to aggregate by.

Add: To add CrossCount fields, select a Calgary database field from the Fields area and select Add or double mouse click on a Calgary database field.

Edit: To create a custom CrossCount field, highlight the field to customize and select Edit. The Custom CrossCount Field interface enables users to construct a new value to cross count or to modify an existing cross count value.

CustomCrossCount Field Interface

  • Field Name: The name of a custom cross count field.

  • Cross Count Values:

    • New: To create a new cross value, select New.

    • Copy: Creates a copy of the selected cross count value.

    • Up/Down: Reorders the cross count values.

    • Delete: Removes the cross count value from the list.

  • Value Properties

    • Value Name: The name of the new or modified value.

    • Fields: The Fields area will display every indexed field available to query from.

    • 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.

      For further information on how to query a Calgary database, visit Calgary Input Tool for more information.

    • Up/Down: To reorder CrossCount fields, highlight the field(s) to reorder, and click the UP or DOWN buttons until the desired field position is achieved.

    • Delete: To remove CrossCount fields, highlight the field to remove and select Delete.

Cache Size: Define the size of the cache for optimal performance in order to easily and quickly access cross counted data. By default, the cache size is set to 256 MB.

Additional Query Criteria Tab

Users can filter the Calgary database. Visit Calgary Input Tool for more information.

Nesta secção: