Dynamic Input Tool
Dynamic Input Tool
One Tool Example
Dynamic Input has a One Tool Example. Visit Sample Workflows to learn how to access this and many other examples directly in Alteryx Designer.
Use Dynamic Input to read from an input database at runtime and dynamically choose 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.
File Schema Errors
To prevent errors, verify the schemas of all files you read are the same.
The Dynamic Input tool can improve processing speed when you read a relational database across a network. It can perform spatial queries on relational databases that don't contain spatial objects.
Configure the Tool
1. Select an Input Data Source Template.
Note
Select Edit . . . to select a path to a file or database table. Visit Input Data Tool for more information.
2. Select how input data sources update at run time.
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 a 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 you want to modify the file, database table, or path name.
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: Use a SQL query to select what records and values the database returns.
Select Add to include a new clause. Each option opens a new window when selected.
1. Pass a Field to the Output: Append a selected column to the data from the database.
Append a Column to Output
Output Field: Select the column you want to append. All columns you pass to this tool are available.
2. Replace a Specific String: Replace any instance 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 with the string you've entered. All columns you pass to this tool are available.
3. SQL: Update WHERE Clause: Change the WHERE clause to replace a static value with the value of the selected column. All columns you pass 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 you replace.
Text to Replace: The text from the existing WHERE clause that you replace.
Replacement Field: Select the column that contains the value to replace the entered string. All columns you pass to this tool are available.
Group Replacement Value for SQL IN Clause: Determine how the IN 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.
4. SQL: Spatial Filter: Use 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.
5. SQL: Update Stored Procedure: Change 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.
Select Edit to change the parameters of a clause.
Reorder the clauses via the Up and Down buttons.
To delete a clause, select it and select Delete.