Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

Alteryx Designer Desktop Discussions

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

Dynamically Update SQL Code for Input

adam_rafael_18
8 - Asteroid

Hi,

 

I want to build an input where the "where" function can not only be modified but also additions/subtractions can be made. For example, if we are pulling data from our trial balance, we want to be able to filter (but not required):

1) Year

2) Account

3) Functional Currency

 

So our current code (slightly modified for simplicity) is:  

where a.acct = '100000'
and a.period_year = 2022

 

But we want to have the option to remove these so it could just be:

where a.acct = '100000'

 

or have the option to add to it so it could be:

where a.acct = '100000'
and a.period_year = 2022

and a.func_currency = "USD"

 

Is this possible? If so, what would the approach be?

 

2 REPLIES 2
apathetichell
19 - Altair

Yeah. I do this. I'd use combo of interface tools/append tools, a summary tool to concatenate the query and finish it with a dynamic connect in-db...

 

The exact "how this all works" depends upon your database and your specific use-case - but the short is yup. Totally doable.

Hello @adam_rafael_18 

 

You can make your SQL query dynamic as mentioned by @apathetichell . You can do this using regular in memory tools as well as INDB ones, depending on your data and use case.

 

In the example below, the text tool has a query and alias. Then a formula tool was used to append to the query or even replace it.  After that, you can  send the data to the "Dynamic input in-DB "to query your DB OR  you can use a dynamic input to replace a dummy query with the one you just built.

 

christine_assaad_0-1657230920170.png

christine_assaad_2-1657231024602.png

christine_assaad_3-1657231058203.png

 

christine_assaad_4-1657231099323.png

Hope that helps! Cheers!

Labels
Top Solution Authors