Alteryx Designer

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

modify sql based on user prompt

Highlighted
5 - Atom

I'm a new user, trying to figure out how to prompt a user for a month, then use that input to modify an SQL where clause.  Should be simple but I can't figure it out.  Could someone please spell it out for me, something a bit more detailed than "use a dynamic input tool"?

Highlighted
12 - Quasar
12 - Quasar

Hi @bt275567 ,

 

I have an example of how you could build this. 

 

I used a text input tool to allow me to enter start dates and end dates.

 

ggruccio_0-1586553909493.png

 

Then connected to dynamic input tool.  Select this option:

 

ggruccio_1-1586553963048.png

 

Select the specific where clause you want to modify:

ggruccio_4-1586554248783.png

ggruccio_5-1586554293950.png

 

 

 

Then under add choose the option that says SQL Update WHERE Clause choose Add.    

ggruccio_2-1586554002871.png

 

This will allow you to pick specific WHERE clauses where you can modify the text in the SQL.  In the instance below I'm replacing the "dummy text" 20APR2009 with the value in field 1

 

ggruccio_3-1586554101026.png

 

Since you want to prompt a user - will you be building it out as an app?   If so that adds a little bit of complexity as you will need to choose what interface tools will allow the user to enter values.  Or if going to share as a workflow you should be good to go!

 

I held back a bit on the screenshots for confidentiality reasons etc....but these should help.

Highlighted
17 - Castor
17 - Castor

Hi @bt275567 

 

Here's an easy way to start

 

danilang_0-1586554088315.png

The text input just provides a dummy field to get the process started.  The formula tool contains the entire SQL statement.  The drop down contains a list of month in this format

 

Jan:1
Feb:2
Mar:3
Apr:4
May:5

 

When the user selects a value, say May, the value 5 is passed to the action tool, which replaces the "1" in "t.month=1" with 5.  This new string is passed to the Dynamic Input tool which is set to replace the entire template SQL string with the new one

 

danilang_1-1586554421280.png

 

You'll have to add in your own connection and template SQL string to be replaced, since you won't be able to access any of the data sources that I could set up.

 

Dan

 

 

Labels