◄Show Table of Contents

Dynamic Input Tool

The Dynamic Input tool reads from an input database at runtime and dynamically chooses what records are read in. Designer filters the database table content and returns only the data that meets the specified criteria. The returned data is joined to the incoming data stream.

The Dynamic Input tool can improve processing speed when reading a relational database across the network, and can perform spatial queries on relational databases that do not contain spatial objects.

To prevent errors, verify the schema of all the files you are reading is the same.

Configure the tool

1. Select an Input Data Source Template.
   • Click Edit... to select a path to a file or database table. See Input Data Tool.

If reading a relational database that contains multiple tables, the Choose Table or Specify Query Window displays.

2. Select how input data sources are updated at runtime.
• Read a List of Data Sources: Uses a selected column to perform a selected action on a list of data sources.
Select and manage a list of data sources

Field: Select the field, or column, that contains the value to modify the file, database table, or path name. All columns passed to this tool are available.

Action: Select how the file, database table, or path name is modified.

• Append Suffix to File/Table Name: Changes the specified file or database table name by appending the value of the selected Field as a suffix.
• Prepend Prefix to File/Table Name: Changes the specified file or database table name by prepending the value of the selected Field as a prefix.
• Change File/Table Name: Changes the specified file or datable table name based on the value of the selected Field.
• Change Entire Path Name: Changes the specified database input path based in the value of the selected Field.

• Modify SQL Query: Uses a SQL query to set which records and values are returned from the database.
Modify your SQL query

Click Add to include a new clause. Each option opens a new window when selected.

• Pass a Field to the Output: Appends a selected column to the data returned from the database.
Append a column to output

Output Field: Select the column to be appended. All columns passed to this tool are available.

• Replace a Specific String: Replaces any instances of a string in a database with the value from a selected column.
Replace string with column value

Text to Replace: Enter the string that appears in the database.

Replacement Field: Select the column that contains the value to replace the entered string. All columns passed to this tool are available.

• SQL: Update WHERE Clause: Changes the WHERE clause to replace a static value with the value of the selected column. All columns passed to this tool are available.
Modify WHERE clause to use column value

SQL Clause to Update: Select a clause to update. All predefined SQL clauses are available.

Value Type: The data type of the value being replaced.

Text to Replace: The text from the existing WHERE clause that is being replaced.

Replacement Field: Select the column that contains the value to replace the entered string. All columns passed to this tool are available.

Group Replacement Value for SQL IN Clause: Determines how the clause is processed. By default, an individual query is run for each clause. When selected, a single query is run unless the character limit is reached, which causes the single query to be divided into smaller queries. The box is automatically checked if the query contains an IN clause.

Character Limit for IN Clause: The size limit is the maximum number of characters that can be used in a query.

• SQL: Spatial Filter: Uses a polygon object in the incoming data stream to determine if existing latitude and longitude coordinates in the database are contained within the object's bounding rectangle.
Filter coordinates based on polygon

Latitude Field: Select the column from the database that contains latitude coordinates.

Longitude Field: Select the column from the database that contains longitude coordinates.

Fields are Integer (multiplied by 1000000): Reports the data format of the coordinates. By default, the coordinates are assumed to be decimals. Select to indicate that coordinates are formatted as whole numbers.

Spatial Filter Field (Polygons only): Select the column that contains the polygon object the coordinates are evaluated against. All polygon objects passed to this tool are available.

• SQL: Update Stored Procedure: Changes a procedure based on the value of a selected column.
Update stored procedure parameters

Parameter to Update: Select the from the list of parameters within the database.

Parameter Name: Automatically populated by selecting a parameter.

Value Type: Automatically populated by selecting a parameter.

Text to Replace: The text from the existing parameter that is being replaced.

Replacement Field: Select the column that contains the value to replace the entered string. All columns passed to this tool are available.

Click Edit to change the parameters of a clause.

Reorder the clauses by clicking Up and Down.

To delete a clause, select it and click Delete.

Alteryx.com | Community | Customer Support | Documentation Feedback

©2018 Alteryx, Inc., all rights reserved. Allocate®, Alteryx®, Guzzler®, and Solocast® are registered trademarks of Alteryx, Inc.