Alteryx Designer

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

Gather list of all field values in DB - Iteratively change field name and table schema

8 - Asteroid

Hi All,


My team is currently in the process of implementing Collibra. Part of the initial upload, we are using the information schema of our database to upload and define the list of columns, tables and databases we have. Additionally, we are looking to get example values for each column (Ideally the top 10 most frequent occurring values for each field - so that we dont blow up the database when trying to query, like instances where we have a unique identifier). 


So - The gist of the issue here is that I cant figure out how to iteratively change the field name, and table name of the query and then spit out a list of values for each column across the database. i.e. Grab all distinct values for column in table, move to next column, after all column values in that table are listed out, move to next table. 


I've attempted to use a batch macro with update formula, but I keep encountering a schema related error as it runs. Has anyone attempted at fulfilling a similar kind of request? What would be the best way to generate the list iteratively. 

ACE Emeritus
ACE Emeritus

First, can you confirm that the process works if you only feed in one table/column?

If it does, the below might be helpful:


Inside the batch macro, try going into the View->Interface Designer and selecting "Auto Configure by Name" or "Auto Configure by Position" in the properties tab of that screen (under the wrench icon).

You may also want to check off "output fields change based on macro's configuration or data input", but it is not required.


That should fix the schema error you are encountering.

8 - Asteroid

I can confirm that the process works when i feed multiple columns and a singular table name, but cant seem how to incorporate getting it to switch tables. I've attached a screenshot for reference - Although I am no longer getting errors thrown, i just dont see anything in the output.


Heres a quick rundown of the below screen shot:

  1. control parameter is set to the Column name
  2. Using "replace a specific string" i update the input node with the control parameter that is being passed through
  3. use the field info to get source info
  4. Parse and filter source info to append the table name as a field 
  5. Filter out nulls
  6. Add ranking to popularity of field value since it came out sorted via the input node


Batch macro issue.PNG

8 - Asteroid

So I was able to figure it out. 


Using this forum question/answer as inspiration:


Breakdown of how i did this:

  1. Create separate control parameters for the table, and field names
  2. Use Action tool to update value. I then selected the table, and field values respectively to be updated
  3. Pass the action tools down to the text input
  4. Text input should be your query split out into several different cells. Have a singular cell for the table and column as they are repeated
  5. using formula node, create the query by creating a field that concats all of the split out cells from above .. essentially do [select]+[column]+[from].. I can provide a screenshot if theres interest
  6. Also create a database connection field
  7. Pass that info through using dynamic input in-db using the newly created fields
  8. Data stream out
  9. to bring table name back in i use append fields 
  10. filter nulls
  11. create rank
  12. transpose field name so i can have that as a field value
  13. dynamically rename fields to my choosing

batch macro resolved.PNG