Using values from a spread sheet to query SQL server

johnyd01

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.

JonA

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)

• 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

• 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-Tool/ta-p/17431
3. Lastly, the Community has an incredible 'Tool Mastery' series that has featured the dynamic input tool in the past.