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.

Creating an analytic app that allows a user to select which database tables to join

D_Y
8 - Asteroid

Hi Alteryx community

 

I'm trying to create an analytic app that allows a user to select which two database tables to join, and to join on which table field/ID. 

 

I've created analytic apps before, so I was able to create one that can select which (single) database table to extract. However, I'm not sure how to apply this to the above situation. 

 

How can Alteryx join two database tables via user input in an analytic app (and allow the user to select which table field/ID to join on), which still allowing the second table input to be optional if the user just wanted to extract one database table?

 

Thanks in advance for any help

  

5 REPLIES 5
apathetichell
18 - Pollux

There are a lot of questions here - so let's break it down.

 

preface: start with a text input - your text input should have your base query and your connection. something like:

"select * from TABLE1 inner join TABLE2 on TABLE1.COLUMN1 = TABLE2.COLUMN2" as your query and "Snowflake" as your connection (or whatever your connection is).

 

1) How do you create an app that can allow the user to select two tables to join.

 

a) You need a list of your two tables. I would then add these tables to two drop downs.

b) You need a list on your potential columns. I would add these columns to two drop downs.

 

2) How do i add the user selected values to my query: you use your action tools. Dropdown 1 replaces TABLE1, Dropdown2 replaces TABLE2 ETC.. You can do this by connection the action tools to the text input tool.

 

This is then fed into Dynamic Input In-DB.

 

You now have your dynamic join.

 

Question Part II - what if there's only one table. Now you'll need to add radio buttons - something like "One table?" "Two tables?" you will bury your second drop down for table - and both column drop downs under the two tables radio button in Interface Designer (with collapse when deselected).

 

Now add a formula tool in between text input and your dynamic input in-db

You will use this formula tool to test if TABLE2 has been updated. Why? because if TABLE2 hasn't been updated - you know that two tables was not selected. You can use some fancy detours/containers to do this - but let's just Formula (and note - this assumes you don't actually have a table called TABLE2.).

 

You use regex to replace the inner join component of your query. I'd use:

if contains([Query],"TABLE2") then regex_replace([Query],"^(.*) inner join (.*)$","$1") else [Query] endif

 

Try building this/customizing it and let us know where you hit stumbling blocks.

 

 

alexnajm
16 - Nebula
16 - Nebula

Hi @D_Y,

 

My gut reaction would be to build a chained app - the first app would allow the users to select the databases to compare and/or simply read in the single database, and the second app would take the column names of the databases and have them available to select as the keys. Then the output of the second app would be the joined tables together.

 

Alternatively you could have this all in one app, but the user would need to absolutely know the table name and the column names to join on (either via a drop down or a text box interface tool).

 

-Alex A-N

apathetichell
18 - Pollux

@alexnajmI'd strongly recommend that the builder start with pre-identified columns that an end user could join. otherwise one should take the type and name of the column out of the db using sample in-db 1/data stream out/field info. This gets to be fairly complex fast if you need to prevent the end user from doing something stupid (inner join on non-unique multi million row db vs other non-unique multi million row db.)...

 

My hope would be @D_Y would build this workflow themselves since building In-DB workflows really requires access to the end users data sources/schemas/etc...

alexnajm
16 - Nebula
16 - Nebula
D_Y
8 - Asteroid

Thanks for your help @apathetichell! I'll take a look through your steps and see how it goes

Labels