This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 04-25-201610:57 AM - edited on 07-27-202111:41 PM by APIUserOpsDM
Dynamically adding data into your workflows is one of the really great things about Alteryx. Having the ability to be able to adjust what you are bringing into the tool can create tremendous efficiencies. So how do we go about doing this? One example is to use the Dynamic Input Tool. This tool allows you to make adjustments to your SQL queries, Stored Procedures or bring in files with the same schema.
Configuration of the Dynamic Input Tool-
Input Data Source Template -Connect to your data and choose the tables and fields or files you would like to bring into the workflow. This is a “Template”, so you are able to change this template with the configuration of the tool, but this “Template” is necessary to bring in the data.
Note: if your data has different schemas then you want to either fix the schema or create a batch macro that will ignore schema and either load data in by name or position
Read a List of Data Sources -This section is used for bringing in a list of files, changing table names, or adding Suffix and Prefixes to your Table Names. If you are bringing in Excel files, the sheet name will need to be added to the Full Path using a Formula Tool.
Change File/Table Name: Takes the specified database table name and changes it based on the values in the specified field.
Change Entire File Path: Takes the entire specified database input path and changes it based on the values in the specified field.
Append Suffix to File/Table Name: Takes the specified database table name and appends a suffix to it. The suffix is the value in the specified field.
Prepend Prefix to File/Table Name: Takes the specified database table name and prepends a prefix to it. The prefix is the value in the specified field.
Modify SQL Query:
Pass field to the Output: This will allow you to pass fields that you may want to use downstream that will append to your data.
Replace a Specific String: This modification is great if you want to pull different data from your tables or fields. You can also use this to update the sheet name from an Excel spreadsheet if you are querying a specific range of cells. To use this modification you will want an input connection that has a field that you want to replace the specific String with. In the replacement window you will remove any text that you do not want to be replaced, then choose which field will replace the string.
SQL: Update Where Clause: This works similar to the replace string, the difference is that the Where clause will be the only portion that you will be able to update. The Where clause will appear in the Update window. You will be able to specify which portion you would like to update and from what incoming field. Please note that the Where portion of the statement will only show. If you have a statement that has Where (the clause) AND (another clause), the Where (the clause) will only appear in the window.
SQL: Spatial Filter: Does you database have latitude and longitude coordinates for stores, business, or customers? Have you built polygons for trade areas or customer profiling and you want to see who in your database fits into these polygons? This is the filter you will want to use. The filter will determine whether or not your data falls within the bounding rectangle of the polygon. Allowing you to look at the data contained within the polygon only.
SQL: Updated Store Procedure: Stored Procedures in SQL are great, but sometimes you need to make small adjustments to them. This Update will allow you to choose the Parameter to update and use a field from your data to replace a specific part of the Stored Procedure.