Alteryx Designer Discussions

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

Macro help importing multiple db tables and outputting them separately into a workflow

Amanda_Breeze
7 - Meteor

Hi team,

I have a PostgreSQL database containing multiple schemas with similar tables. I'm trying to import these tables into a workflow separately so I can work with them. The goal is to union them together. BUT the tables do not have the same structure – while a lot of the columns are similar, they're not the same, so because of this, I need to process them first.

Rather than having multiple data import tools that import the tables' data individually, I'm trying to build a macro that can do this for me, especially seeing that one input tool is capable of accessing all the schemas and tables I need.

 

This macro needs to;

  1. Import data from a specific table within multiple schemas in a postgres database,
  2. Obtain the schema and table names from the output of a SQL query that retrieves them from the information schema.
  3. Dynamically input the data, add the table schema as a new column and output the data to my workflow
  4. Move onto the next schema
  5. Keep going until it has extracted all the tables I need.
  6. Vary the number of outputs depending on whether a filter is applied that specifies which tables to import

 

The issue I'm running into is it seems macros are designed to union (knit) the data together and output that. It won't even import the data if the table structures differ.

I don't want it to do this. I just want the macro to get me the data from these tables and provide them as separate outputs in a workflow.

So, for the moment, I'm only able to retrieve data from the first table.

 

I've attached an image of my macro attempt. I can't provide the real thing because that won't work. Hopefully, you can get the jist of what I'm trying to do though.

 

I've scoured the internet for a solution to this problem. I'm hoping someone here can help.

 

Cheers, Amanda.

5 REPLIES 5
danilang
18 - Pollux
18 - Pollux

Hi @Amanda_Breeze 

 

Unfortunately, there's no way to do what you want.  You can't dynamically add output connectors to a macro.   This is why the Python and R tools come with 5 output predefined, even though I've never come across a case that used more than 3.  The best that you can hope for is to pre-create as many outputs as you think you might need and then direct the data to the correct output according to the iteration number.

danilang_0-1631625653458.png

danilang_1-1631625711991.png

 

Dan

 

Amanda_Breeze
7 - Meteor

Thanks @danilang, I figured I may have been biting off more than I can chew. Your suggested solution should be suitable enough. I just need to manually add connectors as needed.

Thank you for your response.

Cheers,

Amanda.

 

P.S. is outputting multiple separate datasets into a workflow really that uncommon?

Amanda_Breeze
7 - Meteor

 

Hi @danilang , I'm still having issues getting that macro to work though. Previously I used a dynamic input tool but it doesn't like the tables being different, and throws really strange warnings containing Chinese characters (wtf?).

If I switch that out for static input tool or even a macro input and still get "The field schema for the output "Output" changed between iterations.

Seems it really doesn't like cycling through tables of different sizes.

Consequently I only get the first output, the rest fail.

Screenshot macro attempt error.pngScreenshot macro attempt error2.pngScreenshot macro attempt error3.pngScreenshot macro attempt2.png

danilang
18 - Pollux
18 - Pollux

Hi @Amanda_Breeze 

 

Set these options in the Interface Designer - Properties in the macro

 

danilang_1-1631711010763.png

 

Dan

 

 

 

 

Amanda_Breeze
7 - Meteor

Thank you so much for your help @danilang. That works a treat!

Labels