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

Connecting data with multiple tables in a database

rajatjain1
8 - Asteroid

Hi,

 

I have like a 100 tables in a database and all of these tables contain one column which is common to all. I want all the distinct values from this common field of 100 tables in a sheet.

 

Since there are 100 , i don't want to reference each of the tables individually in a query or by the use of 100 different connection strings !

Anyway out to reduce the effort ?

Thanks,

Rajat 

3 REPLIES 3
MichalM
Alteryx
Alteryx

@rajatjain1 

 

You will be able to achieve this using the Dynamic Input tool. You will feed in the list of 100 tables you'd like to query, dynamically update the reference to the table in your SQL one by one and get the output nicely unioned into one big table in the end. 

 

We've covered how the tool can be used in our Tool Mastery series.

JoeS
Alteryx
Alteryx

Hi @rajatjain1 as @MichalM  has mentioned you'll want to use the Dynamic Input tool to do this for you.

 

You can also combine it with a quick Select statement to return the list of tables as well:

 

Select * From Alteryx.sys.tables

 

This will return all the tables from he "Alteryx" database, so please update this to be the one in question.

 

It should give you a helping hand in creating the list of 100 tables as well.

 

I have then built a quick select statement after:

 

"Select [MyColumn] from [" + [name] + "]"

 

Please update "MyColumn" to be the column you want from all the tables.

 

Then lastly you'll need to update the Dynamic Input tool to point to your SQL Database.

 

Workflow.jpg

rafalolbert
ACE Emeritus
ACE Emeritus

Hi @rajatjain1,

 

Dynamic Input tool will be your best friend here, see my example below:

 

1) i've created 2x tables in Teradata to do this (2nd column is different between them when it comes to field type and values present):

 

img1.JPG

2) I've listed all tables in a column and created a macro to query them one by one - this is not ideal as connection needs to be opened and closed each time, which is an expensive procedure (time-wise), but it gives you the isolation, allowing you to address the setup of every table dynamically (different schema, column names, types etc.) - make sure to select one of the auto configurations for your macro (Ctrl + Alt + D) -> Properties, as shown below:

 

img2.JPG 

 

Inside the macro i've appended the source, to allow you to do the joining and manipulation after all data is brought into the main workflow -the approach here collapses all headers into 1 cell and all records into 1 cell: delimited by newline for each record and comma to designate individual fields:

 

img3.JPG

 

3) the final result looks like this and involves dis-assembling the collapsed records and headers:

 

img4.JPG

 

Please know that you'll have to connect to your own database to make it to work (i had to break the connection, so when you load the macro it will prompt some errors).

 

Thanks,

Rafal

 

#Excuse me, do you speak Alteryx?

Labels