Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Batch / Iterative Macro Loop over List of Tables in DB & Apply Basic Profile Union Results

willhaye
8 - Asteroid

Hi, Struggling a bit to create a Batch (or possibly Iterative) Macro to loop over a list of tables and apply a basic profile tool to each table.

 

End Goal: I'd like to be able to take a list of tables (schema.table_name) from an Oracle database which is a subset of the tables in a database, loop through each table and apply the Basic Profile Tool and ideally add the name of the table to the output, union the results from the Basic Profile Tool and then transpose the final output.  By analogy with the videos on input multiple excel sheets it should be straightforward but I'm not getting it.  Advice would be appreciated.

 

Input file:

TABLE_NAME    <<HEADER ROW

SCHEMA.TABLEFIRST   << LIST OF TABLES TO LOOP OVER

SCHEMA.TABLESECOND

SCHEMA.TABLETHIRD

 

Current version of the workflow along with the error indicating that the second and future files are skipped because it doesn't match the number of fields.  This is the template condition not being met but in this case the fields won't match until after the basic profile tool is run.  What am I doing wrong here?

 

PROFILE.PNGPROFILE ERROR.PNG

 

 

Ideally, I'd like to take the tablename and add it as a column after each table is profiled and then union the results from *all* table profiles so I can transpose the output so the fields coming out of Basic Profile are column headers.

 

Your collective help in setting me straight would be appreciated.

6 REPLIES 6
CharlieS
17 - Castor
17 - Castor

Here's how I would go about this:

- The only input you need to this macro is the list of table names (as the control parameter).

- Use a regular Input tool so there aren't troubles with field schema differences like you've encountered with the Dynamic Input tool 

- A second action will add the table name to all records using a Formula tool.

- Since this is a batch macro, all the results will be unioned so you can transform the data from there how you prefer.

 

20190118-ProfileTables.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

willhaye
8 - Asteroid

Thank you Charlie S.   Appreciate the input.  I also did come across a @LordNeilLord post:  https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-can-we-loop-through-and-extract-mu... which is also close enough.  I'll take some time on Tuesday when i'm back in the office to write it up in detail. Thanks again for the input.  More to come.

willhaye
8 - Asteroid

Using a combination of advice from @CharlieS and a former post from @LordNeilLord, I think I have a good integrated solution.  I think it can be improved if anyone has the patience. As seems to be usual it is incredibly simple when it finally worked.  Enclosed is the SQLServer version.  I have a note at the end for the modification necessary for the Oracle version. Here is a full summary.

 

Goal: Run the "Basic Profile" tool for multiple tables in a database based on a , union the output from all tables and then transpose the data fields so they are columns. 

 

Workflow:

BasicProfileTablesWorkflow.PNG

The filter is just to keep other tables in the schema out of the output.  Note the macro after the RecordID tool.  What goes into the macro is a two column list with ID (connected to the control parameter) and the table list.  Also, in the macro control, i had to select the groupby parameters to both be RecordID.

 

Batch Macro:

SimpleBatchMacro.PNG

For the macro, I had to configure the dynamic input tool to point to the same database and selected a specific table as the example.  Note I didn't need to explicitly include the control parameter in this case.  I think trying to stuff it in *and* not putting the recordID outside of the macro was the key problems I was having.

 

NOTE: Oracle Database:  Use this command to find the full list of tables to start from: 

SELECT table_name from all_tables where owner = 'USER_U_R_INTERESTED_IN'

 

Hope this helps others too.  This is a simple way to profile a subset of the tables in the database pretty quickly.

 

Thanks to the community in general and @CharlesS and @LordNeilLord for their direct or indirect contributions to my learning.

willhaye
8 - Asteroid

... Better package export.  Looks like I forgot the macro in the above.  -Hayes

FORNARMA
5 - Atom

Hello, i'm using an older version of Alteryx, I have the same need of yours to profile multiple tables at once.

Could you export the workflow in a way I can use it with my version?

2018.2.5.48994

Thanks

 

CharlieS
17 - Castor
17 - Castor

Hi @FORNARMA 

 

Here's a good article from the Information Lab about downgrading workflows to work on older versions. This should be all that's necessary to get the attached solutions working on 2018.2.

 

https://www.theinformationlab.co.uk/2018/07/12/downgrading-alteryx-workflows/

Labels