Modifying SQL Query using the Dynamic Input Tool:

[DanM]

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.

[MGA]

Thanks for posting! dynamic sql is something I'm very interested in. 

However, upon download (to the most recent 10.1) it indicates that macros

are required for this, can you advise which are needed? thanks

[TaraM]

@MGA - Sorry About that! The custom Macro was removed and the workflow was replaced. 

[Parvathy]

Hi,

I have 50 input tables in my OLEDB source. I have to profile each table one by one and save it to a same destination along with the table name. Can you please help me in dynamically taking the inputs fro profiling.

[hflinn]

Dynamic Input is one of my go-to tools!! Is there an equivalent tool (or a workaround solution) to pass parameters and modify a SQL query for the in-database tool set? 

[DanM]

@hflinn We do have the Dynamic Input In-DB tools that work similar. The Dynamic Input In-DB tool is used in conjunction with the Dynamic Output In-DB tool when creating an In-DB macro for predictive analysis. The Dynamic Input In-DB tool can take In-DB Connection Name and Query fields from a standard data stream and input them back into an In-DB data stream. InDynamicInput

[atamagaii]

Hi, 

As i understood from the alteryx help, dynamic input requires input file and connection(which should be same to create a workflow).

What if i have one huge input db and i want to compare that dynamically with other input file which i can update periodically(like text analytics) to reduce the time in workflow and make the search easy .

Can i use Dynamic input tool for comparison (find and append) purpose?

[DanM]

@atamagaii

The Dynamic Input tool isn't really used to pair down data using a comparison type function. It would allow you to only bring in data that met certain criteria using the template in the Input Data Source Template.  My suggestion would actually be to use the In-DB tools to have the tools create the query you want based on that input file. You would use a combination of the Connect In-DB (for the larger database), then the Data Stream In to bring in your updated file, then use the other In-DB tools to create the actually query. That would allow for speed and the flexibility without hitting the database right off the bat. Once the In-DB tools are completed they will run the query that has been built with the tools. You can find more information on the Community and in your Help about the In-DB Tools.

[atamagaii]

@DanM

Hey Dan,

Thanks for your kind input but i have few questions related to the same

1. If i have an input data of 1000 rows in .yxdb file, can i use dynamic query to find a single/multiple output from it, as i can see, i have to update the where clause but .yxdb extension do not provide me with any where clause option.(Highly appreciate it if you have an example for it)

2. I also worked on in-db workflow which is giving me issues for larger data. I worked on two queries separately, which was working fine but when i joined them together as per the recordID, It is giving me below error

ORA-01652: unable to extend temp segment by 64 in tablespace  

I know that there can be a default limit of data but if i take the output from datastream out and then join them together, the query take way longer time and if do the queries through in-db workflow, it shows above error

Is there any way to reduce the amt of time? I would appreciate a suggestion on  the same.

[DanM]

@atamagaii

1. You can query to find single/multiple outputs, but you would have to use the Alterys tools to do that. You won't be able to do that initially in any of the Input tools with a yxdb. You could more than likely use the Filter tool with an expression to find the single/multiple output depending on how you are attempting to get to your output.

2. The error you are getting is an Oracle error from your database and not Alteryx. You can Google the error, but it looks like you do not have enough temp space to run the query.

Regards

[atamagaii]

@DanM

For question 1:

I didnt understand how to implement the filter for yxdb file. Example: if i take the input yxdb file, connect it to the dynamic input tool through filter or during the editing i have to add some tool? Can you show me an example for my reference?

For Question 2

Yes i checked, that was an oracle error but do you have any suggestion on how to datastream(db format) an input data(yxdb file), so that i can join it with the other workflow. I tried using datastream in or write in-db but both will create a table which i cant do.

[rneerhout01]

I am trying to input a CSV file with the Dynamic input tool that has a file name format of Sev5 [mm-dd-yy].csv. This file is placed there everyday and do not wish to have to select the file by using the Input Data tool. Every time I run the workflow I get the error of too many fields in record #1

 

Text Input is -- FilePath = C:\Users\rneerhout\Documents\Vulnerability Management\Weekly Sev Files\

Formula is -- FilePatch = [FilePath]+"Sev5 "+DateTimeFormat(DateTimeToday(),"%m-%d-%y")+".csv"

Dynamic Input is -- Input Source Template = C:\Users\rneerhout\Documents\Vulnerability Management\Weekly Sev Files\Sev5 9-18-17.csv (previously used csv file)

Modify SQL Query = [Text to Replace] C:\Users\rneerhout\Documents\Vulnerability Management\Weekly Sev Files\SEV5 9-18-17.csv

[Replacement Field] FilePath

I was wondering what I am doing wrong. If some one could help it would be great.

Thanks

Rich

[CameronS]

@rneerhout01  For dynamically reading files - I would recommend using the "Read a list of Data Sources" method on the dynamic input tool instead of the "Modify SQL Query" mode.  The "Change Entire File Path" Action should do it. 

[rneerhout01]

@CameronS

I tried that and it seems that the added information in the Data Preview from the Formula tool is not getting to the Dynamic Input Tool (as seen in the pics below)

[CameronS]

@rneerhout01 That would indicate an issue somewhere outside of the dynamic input tool.  Are you receiving any field conversion errors?  That would indicate the filepath field you are updating with the formula is not large enough to add on the file name.  

[rneerhout01]

@CameronS

I am receiving the following error on the Formula

Thanks

Rich

[rneerhout01]

@cameronS

I increase the field length to 200 with a select after the text input, that removed the truncated error, but still getting the too many fields in record #1

Thanks

Rich

[CameronS]

 @rneerhout01 insert a select tool before the formula tool and use it to increase the size of the field so it can accommodate the additional characters you are adding. 

[rneerhout01]

@CameronS

I am still getting the too many fields in record#1

The file is coming from a Qualys Report formated by Qualys in a CSV format.

Here is the file edited with NotePad++

I was able to bring in a file if converted to Excel, but that would defeat the purpose of using the Dynamic input.

Thanks

Rich

[DanM]

@rneerhout01 are you able to read that file in an Input Tool with no issue?

The files that you are attempting to load, are they the same schema or same Name Fields or Position of the fields the same?  If any of these are different for the files, you will want to take a look at CS-Macro-Dev-Reading-in-multiple-files-with-different-Field . When using the Dynamic Input, the Template must match the files you are bringing in.

Also, if the file location is not going to be different than the Source Template you are using, then all you would need to do is feed it the file name and in the 'Read A list of Data Sources' choose the Change File/Table Name in the Action drop down.

[rneerhout01]

@DanM & @CameronS

I got it to run by changing the Delimiter to the following =====>       \0

Thanks to All

[AndyM]

@rneerhout01 @CameronS @DanM

Looks like your issue is that the column headings are in row 2 of your dataset and the first row is just a file header.  If you change the option on the Input Data tool to read the data starting in row 2 and leave the delimiter as comma, it should work fine.

[tasia]

Hello, I use this tool quite a lot but now need the ability to updated the where clause for multiple fields.  Is it possible to have 

where field1='abc'  AND field2='xyz'

in the SQL query and use this or some other tool to have BOTH 'abc' and 'xyz' replaced by the value of fields1, field2, respectively, from my flow?

Thanks!

Tasia

[DanM]

@tasia You can setup two SQL:Update WHERE Clause and have one field update abc and the other update xyz. When you setup the WHERE clause update it should show you two different Where clauses. You can choose one and the field to update it and then create another one, choose the other WHERE clause and a different field.

[tasia]

@DanM- Thank you for the quick response. I did try that but only one of the two clauses shows up in the dropdown when I go to set up the update where clause. 

So my query says where fieldA IN ( ’abc’) and fieldB IN ( ’xyz’), but in the configuration window only fieldB in ’xyz’ appears as an option under SQL Clause to Update.

I changed to = instead of IN and thereafter was able to set up 2 Update WHERE clauses.  Seems a bit strange, but it works. 

Thanks again.

[bb213]

Do these rules work when you're not connected to a database?  For example, data on your desktop?  Can someone show an example of that? Thanks.

[JessicaS]

Hi @bb213,

Please check out this article for more on dynamic input.  If you cant find the information you are looking for on a local connection,  you may want to post a question in the discussions section.

Thanks,

[ak2018]

I have a SQL Query that goes against Hortonworks Hive using Hortonworks ODBC connection. First, using SQL there itself is finicky as not all SQL stuff works. I have been trying to get to replace the Where clause, but for some reason, Alteryx was not able to read the entire where clause correctly.

so here's a sample of my query:

SELECT
CASE WHEN A.FIELD1 IN ('ABC','DEF', 'GHI','JKL') THEN 'Y' ELSE 'N' END AS FIELD1_IND

, A.FIELD2

,A.FIELD3

,A.DTFIELD4

,A.DTFIELD5

FROM TABLE_A A

WHERE

A.FIELD3 IN ('1234','4567','6789', 1289')

AND

(

(A.DTFIELD4 BETWEEN 20150101 AND 20151231)

OR (A.DTFIELD5 BETWEEN 20150101 AND 20151231)

)

However, Alteryx did not recognize the DTFIELD4 and DTFIELD5 entries at all. On the contrary it also showed me the CASE Statement's IN Clause list.

However, after much head banging, the following has worked weirdly...

SELECT
CASE WHEN A.FIELD1 IN ('ABC','DEF', 'GHI','JKL') THEN 'Y' ELSE 'N' END AS FIELD1_IND

, A.FIELD2

,A.FIELD3

,A.DTFIELD4

,A.DTFIELD5

FROM TABLE_A A

WHERE

A.FIELD3 IN ('1234','4567','6789', 1289')

AND

(

(

A.DTFIELD4 > 20150101

AND

A.DTFIELD4 <= 20151231

)

OR

(

A.DTFIELD5 > 20150101

AND

A.DTFIELD5 <= 20151231

)

)

I wanted to exclude the first start date anyway. Also note that writing the following combination did not work. the comparisons had to be in their own line.

SELECT
CASE WHEN A.FIELD1 IN ('ABC','DEF', 'GHI','JKL') THEN 'Y' ELSE 'N' END AS FIELD1_IND

, A.FIELD2

,A.FIELD3

,A.DTFIELD4

,A.DTFIELD5

FROM TABLE_A A

WHERE

A.FIELD3 IN ('1234','4567','6789', 1289')

AND

(

(A.DTFIELD4 > 20150101 AND A.DTFIELD4 <= 20151231)

OR

(A.DTFIELD5 > 20150101 AND A.DTFIELD5 <= 20151231)

)

[JP2]

Hello,

I might not understand the concept of the dynamic input tool, or if there is something else that does this but I thought I would start here.  I have an input data that connects to our internal tables and am pulling the most recent two months side by side in them including a union in there.  I am trying to put this together using an action item and a pop up box for the users to be able to select the most recent months they want.  For example "prior month" = 201712 and "current month" = 201801.  Below is a snapshot of it but haven't been that successful in having the update value > tie to the update where clause in the dynamic input tool.  Any help would be much appreciated..  JP2

[DanM]

@JP2 Based on your screenshot, the use of the Dynamic tool is incorrect.  The Dynamic tool should be fed either a list of file names and or locations. Or, a list of variables needed to be changed in order to update specific section of database connection strings.

In your case it looks as if you are attempting to bring in specific dates from a table. You will want to use something like the second solution I have presented where you are updating something like a text input tool with the data the user has specified, then updating a section of the connection string to pull in the data.

I gave solutions to both a file and database connections.

If you are attempting to bring in different files, I would suggest using a Directory tool, then a Filter tool to pull the two dates that you want the user to choose. The filter tool would then filter those two files with those dates and the Dynamic Input tool would then bring in those files. In the screenshot below, as long as the files are in the same directory and have the same schema this configuration should work.

If you are attempting  to use database connections and pull specific tables, you will have a different setup. The setup you will want will be to update a specific portion of the connection string which is the dates of the files. I would suggest populating a Text Input tool with two fields, one for each date. Then apply the apps tools to update those fields. You will then use those to fields to update the specific section of the string. Keep in mind the Input Data Source Template is needed as a sample. Make the connection there as a sample and the tool will update that string based on what you  are pushing to the tool.

Check out the sample workflows from the original post. They may give you a better idea of how to update the tool correctly.

[RaghavanR]

Hi,

I am using an input Tool to retrieve data from Teradata using SQL query on the basis of Start date and end date. I want to get these dates as and when the query is run. I saw about using Interface tool and tried them but it is not working even asking me for the dates. Can you please guide me where i am going wrong.

If I have to use Dynamic input tool, how to configure that? Can you please help me?

[RaghavanR]

I was able to get it resolved by @AkimasaKajitani  and @grazitti_sapna. Link to that thread is as below:

Re: How to use Interface tools to update date value in my SQL command (in Input Tool)