community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.9

The latest release includes several enhancements designed to improve your Community experience!

Learn More

Dynamic Input Tool: The file "" has a different schema than the 1st file in the set.

Highlighted
Alteryx Certified Partner

I'm using the dynamic input tool to dynamically change the where clause run in a SQL statement.

 

So essentially I have a bunch of where clauses in a single field in a single table, and then the dynamic input tool is meant to swap out the where clause for each row, run that query and return the result.

 

The problem i am having is that Alteryx is giving me this error for each alternate row:  Error: Dynamic Input (11): The file "" has a different schema than the 1st file in the set.

 

So it runs a query, complains about a row, runs the next query, complains about the next row and so on.

 

I would understand if i was actually reading from multiple files, but i'm not, my source is a single table.
 
I've even changed my source file to from XLS to a sql server table to a cvs, same issue every time.

 

Any ideas?

Alteryx Certified Partner

Capture.JPG

Quasar
Quasar

Hello @shayward without looking at your workflow, it's hard to tell specifically what is going wrong. The link below is for a write up I did for how I use dynamic input to update the where clause.

 

https://dynamicinput.blog/2016/12/13/dynamic-input-instructions/

 

Asteroid

What did you do to resolve this?  All the answers on the subject that I see, seem to use Excel as the input and not SQL.

You need to put the "Dynamic Input" tool into a Macro, as that always gets rid of the Schema error, because when in is run inside of a Macro, it doesn't remember the schema of the last query.

Asteroid

 

I've watched a tutorial on Batch Macro's.  However, I've tried unsuccessfully to put this into a Macro.

I'm using SQL Query and updating the tables name via a list.


All of the Macro posts I've seen reference an Excel or CSV input.

Also, is it referring to my SQL schema?  Because all of the tables in my query are mine.

 

FYI

This works fine on some, but not all. I even export all of the columns with their data types, sorted them and tried running in groups.  Groups with the same amount of columns and data types still produced this error.

 

 

DYNAMIC INPUT ERROR.JPG

In the "Input Tool" the internal value of File as seen within the Action Tool Configuration Window, with the "Input" Tool instead of "Dynamic Input" tool you can make your own Dynamic Input tool.

 

As the "Dynamic Input" tool is great a dynamic updating the single query to anything you want.

 

But the "Action" tool on Top of the "Input" tool in Macro changes everything so can change the database connection and the query, and with multiple queries it isolates the schema changes on your different table queries. 

 

Internally the "Input" tools stores you different file and data connections in a common way as show next.

 

Excel File value is like:           MyExcel.xlsx|||MySheetName

Database File value is like:    aka:DBConnection|||Select * from table

 

Those "|||" pipes are wonderful.

 

 

Asteroid

If I use an Input tool then the macro has no Input connection.
If I convert the Input tool to a Macro then I can't attache an Action tool to it.

 

I have a schema.table for the Input.  I want to replace this with updated tables values based on a list so it pulls in all 30 tables without me having to run each one individually.  

 

So far, I could have had this done manually. 

Maybe a Picture would have been more helpful?

 

Input Tool Made into Macro.jpg

Asteroid

I wasn't able to get it to work as you'd mentioned, probably due to me not fully comprehending what you meant.


However, I was able to get the macro idea to work with the Dynamic Input tool in it.  I think my biggest issue was that I kept trying to use the Dynamic Input tool as the Macro input.

Instead, I used the Text Input tool as the initial "convert to Macro" tool, with only the first SQL table listed in it.  Then dropped in the Dynamic Input tool as I'd been using it.

In the main workflow, I reinserted the Text Input tool with the full list of SQL Tables and attached the action tool to update the table name data values.

Not really knowing what I was doing, I connected the Text Input tool to both the Macro's 'Control GroupBy Field' and 'Input25 GroupBy Field' connections and then output the file.

 

It worked.  At least near as I can tell.  Really not sure what these do: 'Control GroupBy Field' and 'Input25 GroupBy Field'

 

My whole issue was needing to bring in 40 or so SQL tables without having to do one at a time.  All of the online help showed connecting to multiple Excel or CSV files only.

Funny part is, my original method worked fine (not using a macro) for a different set of 40 tables, but would produce this threads error message on these.

 

AP_DYNAMIC INPUT.JPG

Labels