Need to use SQL to search data pulled with Alteryx Workflow

[mwolffe]

Greetings!

I am trying to figure out if there is any way to use data taken from an alteryx Workflow and then run that specific data through SQL as the searchable criteria?  Currently I have to run the workflow and output to an Excel sheet the list of claim ID's.  I then manually copy and paste all of the claim id's directly into the SQL coding and run that separate.  Once the SQL returns my desired data, I then have to copy all of that back into another Excel sheet.  

I am hoping there is a way to avoid the manual steps involved.  I thought about doing a seperate Data Input in the workflow that pulls all of the data and then use the Join tool to find the matching specific claims I need.  The only problem with that is when I pull ALL the data it could return 100k + lines and/or timeout.

Here is my example:

I have a specific list of claim numbers (i.e. '123456789','234567890','345678901'...) and within the SQL coding I need to search for those exact claim numbers under the WHERE cl.claimid IN ( '123456789','234567890','345678901'... )

Does anyone know if it's possible to make my claim id's a wildcard or loop it somehow that Alteryx will pull a claim in the Database then move to the next one?  Or any other suggestion...

Thanks so much!

[mbarone · Accepted solution]

Hi @mwolffe ,

Yes, this is possible and relatively straight forward.  Can never tell how data connections directly to a database are going to work - sometimes there's a few kinks to work out. 

But, once you get the connection up and running to your database, you can pass your SQL string directly.

You'll want to use a Dynamic Input tool to do that.  I'll mock something up and post it here.

[Zas3NfkB · Accepted solution]

If you have your claims list as an excel input file and then link it to a dynamic input tool, you can update the SQL statement that way. You can either have it run iteratively in the dynamic input (so each claim will be ran then combined before the output) or you can combine the whole where clause using the in statement.

[mbarone · Accepted solution]

Attached is the example I mentioned.  I have  SQLite database with a table of IDs and values.

I also have a list of IDs I'd like to query for in that table.

I concatenate the IDs to inject into my IN statement.  I connect that concatenation to a Dynamic Input tool that's configured to search for a SPECIFIC id.  I simply replace that specific ID with the incoming string of concatenated IDs.

Take a look and let me know if you have any questions.

[mwolffe]

Thank you so much!  Your suggestion and example helped me out perfectly!

[mwolffe]

Thank you so much @Zas3NfkB! This is exactly what I needed!  🙂