Alteryx app to dynamically select fields in In-DB select tool

[Tranda]

Hi all,

I know there's a way to use the Alteryx app to dynamically select fields in a Select tool however i just wanted to know if it's possible to do the same for an In-DB select tool

Reason for the question is because i dont know want use keep my query all In-DB instead of needing to use the Data Stream 

[Ladarthure]

What do you mean by dinamicly?

One wsay top do it would be to use the dynamic input in-DB which will allow you to send a query stored in a text field, meaning you can do as many selection as you want?

Would you have a simple example around this?

[Tranda]

Hi Ladarthure,

Thank you for your reply

Below is an example, what i'm trying to achieve here is to use the listbox to control which fields to include in my In-DB Select tool

Currently to do that i'll need to stream the data out and use the green Select tool, this would take time depending on the amount of data i need to stream out

[apathetichell]

The classic way to do this is to use a sample tool to select 1 record - stream that out and then use the list box to set which fields you want in the successive query.

Here's an old workflow which uses that logic: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Dynamic-Select-with-In-Database-Too...

I'd recommend using field info in lieu of field summary.

[Tranda]

Thanks apathichell,

I've never used the Dynamic output In-db tool before but would that mean i'll still need to stream my data out of In-db and then stream it back in to complete the query?

My issue is streaming large amounts of data out and then back in will contribute to the time it takes to run the app / workflow, ideally i would like to keep everything in-db

[apathetichell]

you use a sample tool to stream out one record - that creates the schema for the dropdown tool. Dynamic Output In-Db is used to take the connection (ie the database name) - it does not take data. Other options in DOIDB include things like the Query - no data... Dynamic Input In-DB feeds in the dynamic query because it is awesome and all powerful.

[Tranda]

Thanks apathetichell,

I've almost got what i need, so how do i use the output of the last bit to continue onto a join ?

There's no issue having the data flow into the browser but it doesn't seem to know what fields are available for the join

[apathetichell]

ok. a few things -

1) you have to make sure your join tool is always included in your connect in-db - think about it - if it's not - your join will fail.

2) where is your second source for your join?

3) you will have to hit run to populate metadata - so make sure some basic fields are selected in your join tool.

Can you give more background on what your end goal is here?

[Tranda]

Hi apathetichell,

I currently have an app which is pulling data from 3 different sources, all in-db

I want to give the users the ability to select which of the 3 sources to join via a check box and the ability to choose what fields to bring in from each source

Eg.

Table A Customer

• Customer ID
• Customer Age
• Customer Address
• Customer Height
• Gender

Table B - Customer Hobby

• Customer ID
• Customer Hobby
• Hobby Type

Table C - Customer's favourite food

• Customer Id
• Food type
• Food origin

[apathetichell]

This isn't that straight forward. You will need a chained app set up.

1) select the tables

2) select the fields (parallel instances of the workflow you have)

You want to dynamically create a SQL join statement which the  workflow you are using as a template doesn't do. In this case you want to use a formula tool to create a SQL statement with a join where you have parameterized both the table names and the fields to join on - you will basically need each of the two field selector workflows responsible for creating some of the SQL and figure out the specific syntax needed to template what you need.

You will then feed the SQL with the Join statement into your Dynamic Input in-DB. You can't do what you are trying to do easily with a Join-In-DB. You should also remove your Primary Key from your listbox - you cannot let your user deselect the primary key -you can then append one stream to another and end up with something like:

"Select CustomerID, " + [concat value of fields from list box for table1 separated by comma] + [(start with a comma) concat value of fields from list box for table 2 separated by comma] "from "+ [table1] + " Inner Join On " + [table2] +  " Where " + [table1]+'".customerID" = '+[table2]+'.customerID"

In a formula tool... - you feed that in via dynamic input in db... Not sure where you go from there - but that should get you to the join.

reminder - don't let the user deselect customerid.