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.
SOLVED

Using values from a spread sheet to query SQL server

johnyd01
7 - Meteor

Good morning,

 

New to Alteryx (the product, and hence this community).  After some beginners assistance please; I can't seem to solve an issue.

 

 

Overall objective:  I want to take values from a spread sheet, that I can then use to retrieve data from a SQL server, and finally blend the two different sources.  The spread sheet has unique identifiers in it, that I want to use to query the SQL server, because I don't want to return all the data.

 

For example:

 

Spread sheet has Unique IDs , 112233, 223344, 334455 - there will be thousands and they won't always be the same (i.e. I want to create a repeatable workflow that has different spread sheets as the source).

 

I then want to retrieve (select *) the 'product type' where the Unique ID is equal to 112233, 223344, 334455, and so on.

 

What is 'the best' way of taking all the unique IDs from the spread sheet and using those values to construct the select statement?

 

Appreciate the help and guidance.

 

Regards,

 

JD.

 

 

 

 

 

6 REPLIES 6
JonA
Alteryx
Alteryx

Hey JD. I'll briefly explain how I'd do it, then I'll point you to some helpful resources that show you other examples.

 

  • Start with your spreadsheet as an input data tool.
  • Next, drag a dynamic input tool downstream and connect your spreadsheet to it. 
    • Configure the dynamic input tool by selecting 'Edit...' (here you're defining the template so the values from your spreadsheet will update the query, you're not solidifying the end result just yet)

edit_dynIn_1.png

  • In 'Connect to file or database box, select the connection string to the db you'd like to query from
    • Under options, box 3, select the box with 3 dots to edit the query
    • Enter in a simple statement that will soon be replaced (I used this one as I have a unique list of Customer IDs: Select * From Demo.dbo.Transactions_Dates Where Customer_ID=3)
    • Select the toggle button to 'Modify SQL Query'
    • Then click the 'Add (down arrow)' button and choose SQL: Update WHERE clause - this will read your statement and populate most boxes for you
    • Verify the claus you want to update, the text (or IDs in your case) from your template query you want to replace, and the field in your spreadsheet that will replace the value in your query

edit_dynIn_2.png

  • Click OK and run the workflow

Helpful resources:

  1. Check out this virtual training from our product training page: http://www.alteryx.com/virtual-training
    • Under 'Watch a Past Session' - find 'You Get What You Need - Dynamic Inputs' 
    • The link to playback the recording is here
  2. Here's another link from the Community that will help explain the process in more detail: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Modifying-SQL-Query-using-the-Dynamic-Input-...
  3. Lastly, the Community has an incredible 'Tool Mastery' series that has featured the dynamic input tool in the past. 
johnyd01
7 - Meteor

Absolutely spot on, thank you. 

 

Got this working within minutes.

 

Thank you sir.

nbarai
6 - Meteoroid

Hi Jon

 

Thanks for the detailed explanation/instructions - very useful.

 

Just a quick follow up question... I see there may be a limit in the "SQL IN Clause". What would be the recommened solution if we exceed the limit.

 

King Regards and Thank You.

Naresh

RodL
Alteryx Alumni (Retired)

@nbarai,

In regards to your question on the limitation for the "SQL IN Clause", this setting when checked will allow for a single query to be run (where your query actually contains an IN clause). It will basically combine a list of values brought into the Dynamic Input tool into a list for the IN clause. If the size limit is exceeded by the number of characters coming through the list, it will split up the query into multiple queries.

 

Check out Help for the details.

 

Not certain if this is the reason for this option, but there is a limitation (at least in MS SQL Server) of the characters allowed in a query statement...https://msdn.microsoft.com/en-us/library/ms143432.aspx

Ruchi
5 - Atom

Hi John,

 

I implemented the same method as you have suggested below, but I can see in the output that the connection is establishing after every select query.

I use Hive connection(Cloud DB) and I see the below statement after every select query:

Dynamic Input (2) ODBC Driver version: 03.80

<select query with 1st where condition>

Dynamic Input (2) ODBC Driver version: 03.80

<select query with 2nd where condition>

 

Is there any way to include loop or something so that we can get rid of establishing DB connection again and again.

 

nbarai
6 - Meteoroid

Hi Ruchi

 

I don't think you can just do one connection and subsequently use different queries using the initial connection.

I could be wrong but I believe you try to get as much information as you possibly can in the first connection (this way you reduce the number of odbc connection). I fully acknowledge that the performance may need to be balanced

 

Also..when you start the workflow, the connections are initialised at the beginning

 

Labels