App where user chooses Excel file and data is parsed to filter WHERE clause of SQL

Question

I'm having a lot of trouble doing something in Alteryx that seems pretty basic.  In my workflow(which I'd like to put on a server after completing the app), the user will browse to an Excel file that contains a list of IDs for the given week.  That list of IDs will be taken from the Excel, turned into a comma-separated list and passed into the WHERE clause of a SQL statement.

At this point, even after watching some help videos, I'm still pretty confused on the process of getting the user-input Excel data into the workflow.  If I use the 'File Browse' tool, it looks like I have connect this to an action tool that then feeds into the 'Input Data' tool.  But the 'Input Data' tool needs to be configured with a file.  However, I don't want a file configured because I really want the input data straight from the Excel, which the user will browse to on their own machine.

I browsed a bit and some people seem to suggest you need to create a template 'Input Data' file, but if this application is going on a server, so does that mean I have to host the template file in the same place I host the app?  Again, all I really want to do is use the Excel that a user chooses as the data source.

I'm grateful for any suggestions or sample workflows, and I'm happy to clarify if needed.

jamielaird · Answer

Hi @samc725

Sorry it took me a little while to get to this. Regarding your questions:

Now that I'm able to access the package you sent, I still am confused with how it works based on what you laid out.  For example, you said that "the template is a query rather than a file", but it looks like the template is an Excel Doc with the IDs pre-populated. So it looks like I need to have a pre-populated Excel file for this to work?

The Input Data tool does need a template but this will be replaced by the file the user selects using the File Browse tool when running the workflow as an App. The template simply allows you to build the workflow in Designer with an idea of what the input data might look like, but will be swapped out with whatever file the user provides.

In them Modify SQL Query section, I have after the update WHERE Clause:  "cus.CUSIP" in ('123').  The table cus is generated from a subquery aliased as cus and the field I want to fill in with user input is the CUSIP field.  However, I get an error "The field 'cus.CUSIP' is not contained in the record'.

In the Dynamic Input tool make sure you first setup a connection under Input Data Source Template. This is your template SQL query that you will modify using the data flowing in from the user-selected input. For example consider the following template query as an example:

select
sq.*
from
(select * from temp26022021) sq
where
sq."ID" in (1,2,3)

To replace the template line sq."ID" in (1,2,3) with IDs from your user-provided Excel file you would configure the Modify SQL Query settings as follows:

(Your database may not require quotation marks around the fields - I'm using PostgreSQL here - but the same principal applies).

If it helps, just think of "Replace WHERE clause" as a fancy Find & Replace tool - that's effectively what it is doing; replacing the 1,2,3 portion of the query shown in the first text box sq."ID" in (1,2,3) with a comma-seperated list of values from the Replacement Field you have selected, up to the character limit specified in the numeric box.

samc725 · Answer

Hi Jamie,

Thanks for your continued assistance.  Now that I'm able to access the package you sent, I still am confused with how it works based on what you laid out.  For example, you said that "the template is a query rather than a file", but it looks like the template is an Excel Doc with the IDs pre-populated:

So it looks like I need to have a pre-populated Excel file for this to work?  I recognize that I can't run your workflow with the db details you sent, so I tried to run the code using the DB connection that I need as the Input Data Source Template.  In addition, I replaced you ID_File with a generic Excel file on my local laptop with the ID column.

In them Modify SQL Query section, I have after the update WHERE Clause:  "cus.CUSIP" in ('123').  The table cus is generated from a subquery aliased as cus and the field I want to fill in with user input is the CUSIP field.  However, I get an error "The field 'cus.CUSIP' is not contained in the record'.

Are you able to able to assist with my question regarding the Excel template and the error message? Thanks very much for your help.

jamielaird · Answer

Hi @samc725 ,

I created it using 2021.1, but I've packaged it up for earlier versions (2020.2+) in the attached file.

DynamicWhereClauseFromExcel.yxzp