community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Interactive query while fetching data from database

Atom

Hi All,

 

Greetings!

 

I am new to Alteryx and would like to understand the feasibility of my requirement in Alteryx.

The input is needed to be interactive with the user able to chose a particular date range/location to fetch data from the database.

Is it possible to build queries dynamically as per the user request and fetch the data?

 

Any help/pointers is highly appreciated.

Thanks!

Alteryx Partner

Hello,

 

Yes! This is very possible by converting your workflow into an Analytic App. I've attached a sample workflow which should help you get started, but I'll explain the logic below.

 

You want to add your input tools to collect user input, and connect them to your Input Data Tool, with the Update Value with Formula configuration selection.

 

I've found that the best way to do this is by setting your SQL Query to have:

 

SELECT * FROM TABLE

 

WHERE

1=1

AND 2=2

AND 3=3

 

etc... for as many conditions as you expect to have.

 

When you do this, you can then use the following formula in your update tool:

 

IF !IsEmpty([#1]) THEN
Replace([Destination],'1=1',"[Field1] = " + "'" + [#1] + "'")
ELSE [Destination]
ENDIF

 

This means that the 1=1 will remain if no input is selected for that field, otherwise it will be replaced with the condition you desire.

 

For dates, I generally just set the generic SQL query to [Date] >= '2010-01-01', and then use the "Update Value" configuration in my Update tool with the "Replace a Specific String' checked off, and 2010-01-01 in the box below (note the lack of quotes in my previous 2010-01-01, this is important, at least for SQL server, for the SQL query to be parsed properly).

 

Let me know if you need clarification, or have any more questions about this,

 

 

Spoiler
dynamicsql.PNG

Cheers!

 

Atom

Thank you tcroberts for your help!

I will try to understand the details you mentioned and reach out to you for any further queries.

 

 

Labels