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

[samc725]

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]

Hi @samc725 

I've packaged up a solution that should give you what you need.

A brief explanation:

1) The File Browse interface tool which is connected to the Input Data tool will swap out that input file with the Excel file provided by the user. You don't need to worry about publishing any files to the Server when publishing the workflow as the workflow will use whatever file the user provides, but you might want to add some validation to ensure that the file the user provides has an ID column and the values in it are of the correct data type.

2) The "Input Data Source Template" part of the Dynamic Input tool is where you define the SQL query which should include a WHERE clause. My DB connection won't work for you but you should get the idea of what it's doing from this screenshot.

3) The template is a query rather than a file, so when you publish you won't have the issue of packaging a file, but you will need to make sure that your data connection also exists on the Server (in this case, that there is a DSN named "alteryx_community" on the Alteryx Server machine).

4) The "Modify SQL Query" section of the Dynamic Input tool is where you specify where Alteryx should pull the IDs from the user-provided Excel file.

Hope this helps. Shout if you have any questions.

[samc725]

Thank you for your help, @jamielaird.  

I'm getting an error when I try to open the package in Alteryx which suggests I'm using an earlier version of Alteryx (see below):

However, I'm using 2020.4, which is the most recent version available to Windows users on their website.  Do you know why I may be getting this error and how it can be resolved?

[jamielaird]

Hi @samc725 ,

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

[samc725]

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]

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.