Batch Macro for DB Compare - cannot get all SQL into 1 workflow

[frank6773]

Hello,

I need the ability to compare data amongst 2 DB schemas for purposes of archive testing.  I was able to get a workflow going for comparing 1 set of tables but I would like to build a batch macro to be able to run through all the scenarios I need in 1 workflow.  Forgive me for the attempt on the macro development as this is my first one.  My thought was that I would build the SQL with a table, send it through the workflow, transpose it as I needed, and then combine all the results in one place.  As all the tables that are queried have different fields, dynamic input was failing.  And since each transpose should be done on one of the key fields being input (MAIN_ID), that was also failing as that needs to be dynamic as well.

Can I pull different data tables (building different SQLs) using a macro and transpose them dynamically based on an input field to creating the SQL?  My desired results would be to have a table where the actual/expected value failures would be all in one output file.

The file Data Compare is the workflow that functions as I would like (but with just 1 specific SQL data pull).

The SQL_batch2 macro and simple_batch workflow was my attempt at trying to put together the SQLs (and miserably failed).

Any guidance would be wonderful!  

[DanM · Accepted solution]

@frank6773 

You have the right idea. What you want to do is use the Control Parameter tools along with the Action tools (if you connect a control parameter to the lightning bolt symbol on top of the tool an Action tool will be added automatically). You'll want to add a Control Parameter to the Formula tool and the Transpose tool.

In the Action tool you'll be able to see the variables you have within the tool. Selecting that variable will allow you to update it. 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Control-Parameter/ta-p...

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Action/ta-p/35500

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/t...

You may need to use more than one Control Parameter to update what you want in the SQL or you can replace the entire thing with writing out yout different SQL statements in a Text Input tool and feeding that into the macro.

Attached is what it could look like. I didn't select anything within the Action tools as I wasn't sure what you are updating. I would also review the resources added above to familiarize yourself with the tools and macro.

[frank6773]

Thanks Dan, greatly appreciate the help!  I had started with the control parameter and then talked myself out of it.